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
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
Solved! Go to 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 , 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
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.
@Anonymous Try something like this:
EVALUATE
SELECTCOLUMNS (
ADDCOLUMNS (
VALUES ( Something[ABC] ),
"@DummyColumn", COALESCE ( Something[ABC], 1111 )
),
"Resultant Column", [@DummyColumn]
)
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]
)
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]
)
@Anonymous That's awesome!
@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
@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]
)
What is the ORDER BY for? Have you considered using VALUES() or ALL() instead of SUMMARIZECOLUMNS() ?
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |