Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

SSRS DAX Replace NULL Values

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

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

View solution in original post

15 REPLIES 15
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
AntrikshSharma
Super User
Super User

You can use IF & ISBLANK or COALESCE
Anonymous
Not applicable

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

jorgesam_0-1598540348655.png

 

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

@Anonymous  Try something like this:

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

 

1.PNG

Anonymous
Not applicable

@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

@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
Not applicable

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

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

1.PNG

Anonymous
Not applicable

@AntrikshSharma 

Great! This works perfectly.

Thank you so much!

@Anonymous  That's awesome! 

Anonymous
Not applicable

@AntrikshSharma  thank you for the code

I tried it but seems that the COALESCE is not working

jorgesam_0-1598542745668.png

 

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

@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
Not applicable

@AntrikshSharma 

Thanks, this worked!

lbendlin
Super User
Super User

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

Anonymous
Not applicable

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.

jorgesam_0-1598540229699.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.