topic SSRS DAX Replace NULL Values in DAX Commands and Tips

SSRS DAX Replace NULL Values

Anonymous — Thu, 27 Aug 2020 14:11:17 GMT

Hi,

I've been trying to solve this for some time now, but nothing worked, and couldn't find any specific help online.

I have a report built in SSRS using DAX, and I'm having multi valued parameters.

In order to feed each parameter I have a separate dataset for each, and in this example I will show the Item Category

EVALUATE

SUMMARIZECOLUMNS ('Item'[Category] )

ORDER BY 'Item'[Category] ASC

But the dataset includes the null value, which doesn't allow me to have all the values selected, and also I can't allow null values in multi value parameters.

So what I'm asking is how can I use a similar function to the isnull(,) from SQL?

I basically want to say

isnull(Item Category,'Unknown')

Thanks

Re: SSRS DAX Replace NULL Values

lbendlin — Thu, 27 Aug 2020 14:29:03 GMT

What is the ORDER BY for? Have you considered using VALUES() or ALL() instead of SUMMARIZECOLUMNS() ?

Re: SSRS DAX Replace NULL Values

AntrikshSharma — Thu, 27 Aug 2020 14:31:41 GMT

You can use IF & ISBLANK or COALESCE

Re: SSRS DAX Replace NULL Values

Anonymous — Thu, 27 Aug 2020 14:56:49 GMT

The order by is for the parameter later, to show the later in order.

I tried the VALUES and the ALL function, but the null is still there.

My target it to somehow replace null with a different value, not to exclude it from the report.

Re: SSRS DAX Replace NULL Values

Anonymous — Thu, 27 Aug 2020 14:59:32 GMT

When using filter not blank the null is filtered out but I want to replace it or else the data in the report will be missing

Can you help me with the If & ISBLANK combination? Never used it before. I appreciate your time.

Re: SSRS DAX Replace NULL Values

AntrikshSharma — Thu, 27 Aug 2020 15:16:27 GMT

Anonymous Try something like this:

EVALUATE SELECTCOLUMNS ( ADDCOLUMNS ( VALUES ( Something[ABC] ), "@DummyColumn", COALESCE ( Something[ABC], 1111 ) ), "Resultant Column", [@DummyColumn] )

Re: SSRS DAX Replace NULL Values

Anonymous — Thu, 27 Aug 2020 15:39:05 GMT

AntrikshSharma thank you for the code

I tried it but seems that the COALESCE is not working

I read online that this function is only included in the latest versions of SSRS. Is there a different way to do the same without this function?

Thanks

Re: SSRS DAX Replace NULL Values

AntrikshSharma — Thu, 27 Aug 2020 16:19:03 GMT

Anonymous In that case you can try this:

EVALUATE SELECTCOLUMNS ( ADDCOLUMNS ( VALUES ( Something[ABC] ), "@DummyColumn", IF ( ISBLANK ( Something[ABC] ), 1111, Something[ABC] ) ), "Resultant Column", [@DummyColumn] )

Re: SSRS DAX Replace NULL Values

amitchandak — Thu, 27 Aug 2020 16:56:42 GMT

Anonymous , coalesce is supported after March 2020.

Try like this, this will remove blank

EVALUATE

SUMMARIZECOLUMNS ('Item'[Category], filter('Item', not(isblank('Item'[Category])) ))

ORDER BY 'Item'[Category] ASC

Re: SSRS DAX Replace NULL Values

Anonymous — Fri, 28 Aug 2020 08:00:33 GMT

AntrikshSharma

Thanks, this worked!

Re: SSRS DAX Replace NULL Values

Anonymous — Fri, 28 Aug 2020 09:03:37 GMT

AntrikshSharma

one more question if you can help me.

And If I want to add this code to the main dataset, where I'm also pulling other fields that don't have the null value issue, how can this be done?

For example the below code

EVALUATE
SUMMARIZECOLUMNS (
'Item'[Item ID],
'Item'[Category],
FILTER (
VALUES ( 'Company'[Company] ),
(
OR (
(@CompanyCompany = "All"),
PATHCONTAINS (@CompanyCompany, 'Company'[Company])
)
)
),

"Total Quantity", [Total Quantity]

)

How can I replace the Category with the script that avoids null values?

Thanks

Re: SSRS DAX Replace NULL Values

AntrikshSharma — Fri, 28 Aug 2020 09:20:52 GMT

Anonymous Try this:

EVALUATE SUMMARIZECOLUMNS ( 'Item'[Item ID], 'Item'[Category], 'Company'[Company], FILTER ( VALUES ( 'Item'[Category] ), NOT ISBLANK ( 'Item'[Category] ) ), FILTER ( VALUES ( 'Company'[Company] ), @CompanyCompany = "All" || PATHCONTAINS ( @CompanyCompany, 'Company'[Company] ) ), "Total Quantity", [Total Quantity] )

Re: SSRS DAX Replace NULL Values

Anonymous — Fri, 28 Aug 2020 09:33:30 GMT

AntrikshSharma

Thanks for that but I mean how to add the part

IF ( ISBLANK ( 'Item'[Category] ), "Unknown", 'Item'[Category] )

In the query. I don't want to filter out null values, I want to replace them with a different value, same way as in the previous script.

Thanks!

Re: SSRS DAX Replace NULL Values

AntrikshSharma — Fri, 28 Aug 2020 09:54:42 GMT

Anonymous Since I don't have your data I am only guessing this will work for you:

EVALUATE SELECTCOLUMNS ( ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Item'[Item ID], 'Item'[Category], 'Company'[Company], FILTER ( VALUES ( 'Company'[Company] ), @CompanyCompany = "All" || PATHCONTAINS ( @CompanyCompany, 'Company'[Company] ) ), "Total Quantity", [Total Quantity] ), "NewCategory", IF ( ISBLANK ( 'Item'[Category] ), "Something", 'Item'[Category] ) ), "Items", 'Item'[Item ID], "Cateogry", [NewCategory], "Company", 'Company'[Company], "Quantity", [Total Quantity] )

Re: SSRS DAX Replace NULL Values

Anonymous — Fri, 28 Aug 2020 10:06:05 GMT

AntrikshSharma

Great! This works perfectly.

Thank you so much!

Re: SSRS DAX Replace NULL Values

AntrikshSharma — Fri, 28 Aug 2020 10:16:58 GMT

Anonymous That's awesome!