Friday, June 23, 2017

How to handle NULL in SSAS Tabular by using DAX

As new in DAX area, I found a few useful DAX function that can ease your BI life while you are working with SSAS Tabular model. To handle null values and undefined values were challenging at the begining, however: found two useful function called ISBLANK and IF that you can use to handle those challenges.

Let's discuss with example: a table named Dim_Account which has data like below (In real life you may not have this type of data, however, you get problem when your data is corrupted)

Fig 1: Account data (corrupted)


Now I would like to count number of rows from the above dataset where AccNo is not NULL,and AccTypeName='Savings'

So the DAX look like: COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo]))

Here, COUNTAX calculate only non empty rows so I got number of row return =1; however; I would like see how many rows also have NULL, and would like to replace the NULL value with 0.

How do you do? There is a function in DAX which is ISNULL. So lets handle the NULL value in DAX:

IF(ISBLANK(COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo]));0;COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo])))

If you are coming from SQL background then surely you say NULL and BLANK are not same, they are completely different.. However; here is the reference from Microsoft DAX:

"A blank is a data type in DAX that represents and replaces SQL nulls. You can create a blank by using the BLANK function, and test for blanks by using the logical function, ISBLANK."


However, Lets look into the details regarding function IF(ISBLANK(A);0;A), it means if A is NULL then return 0 and all other cases get the value of A.

At the same way, If we explain DAX for accountype NULL handles:

Fig 2. DAX Null handles

From the above fig 2: first part of the DAX query is traverse through each row of the table and find if any NULL value and then replace that with 0 and the last part is execute if first part is FALSE, it means get all the value than NULL.


Tuesday, June 20, 2017

Missing Report Data Pane in SSRS

One of the reporting tool is used in BI called SSRS.  And when you create a SSRS project by using SQL Server Data Tools, the most important pane you use to create report is ReportData. Literally, you can't make SSRS report without using Report Data Pane, Now think about suddenly you can't find Report Data pane which was just there before. How you gonna find that?
This post will cover how to find report data when you just miss it.

When you create SSRS project, you will find report data pane under view as like below screenshot.


Fig 1: Report Data in Visual studio


And the report data pane look like below:

Fig 2: Report Data Pane
Now you start making report as like below figure:

Fig 3: Design the report
After working a little bit then you may suddenly don't find the Report Data Pane under 'View', And list under 'view' menu will look like below:

Fig 4: missing Report Data

Now you  are puzzled, how come the pane is not there??  How to get it back:
The reason for missing the 'Report Data' is you must click some where else than the design layout; you may click at the project file (as like below diagram)

Fig: Click at the right place



As long as you click at the design layout then you will find the 'Report Data' pane.