SSRS DAX Replace NULL Values

[Anonymous]

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

[amitchandak]

@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

[AntrikshSharma]

You can use IF & ISBLANK or COALESCE

[Anonymous]

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.

[AntrikshSharma]

@Anonymous  Try something like this:

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

[Anonymous]

@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

[AntrikshSharma]

@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]
)

[Anonymous]

@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!

[AntrikshSharma]

@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]
)

[Anonymous]

@AntrikshSharma 

Great! This works perfectly.

Thank you so much!

[AntrikshSharma]

@Anonymous  That's awesome! 

[Anonymous]

@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

[AntrikshSharma · Accepted solution]

@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]
)

[Anonymous]

@AntrikshSharma 

Thanks, this worked!

[lbendlin]

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

[Anonymous]

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.