Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I have a Power BI measure issue that I can use some assistance in solving.
I want to count number of records within a table that are not null, however I still want records with null values to appear with count of 0. I have tried various Calculate DAX expresssions but so far no luck.
Product table example data
Power Bi report
When I add the measure: Measure = Count(Products[Sales]) the Sprocket West record no longer shows
If I change the measure to: Measure = COUNTROWS(DISTINCT(Products[Sales])) The Sprocket West record shows but with the wrong count desired, it is counting rows.
Desired report output would not include the Sales column but should have a measure value of 0 for Sprocket West, not a value of 1 as shown below
Does anyone have any suggestions I can try?
Thanks
Hi @Forthe3kids ,
You can try this measure
Result =
IF(
ISFILTERED('Table'[Sales]),
IF(
SELECTEDVALUE('Table'[Sales]) <> BLANK(),
COUNT('Table'[Sales]),
0
),
CALCULATE(
COUNT('Table'[Sales]),
FILTER(
'Table',
'Table'[Sales] <> BLANK()
)
)
)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Albert,
thank you for the reply, unfortunately this did not solve my problem, records with null value for sales do not appear. Reminder, in the report output i do not want to display the sales nor material columns. I only want product, location and count of sales. This is also a multi table query from an analytical model, thus i cannot transform the data nor use power query and add a column.
Thanks
Dave
Thank you for thie reply, for my simple example, yes the "+0" solves the problem.
My appologies, I should have added that the true business case is a report based on an analytic model using multiple tables (Hundred thousand + records). Using the +0 results in a cross-join between the multi-table joins and will produce horrendous performance issues.
Thank you
maybe you can try this
Measure = Count(Products[Sales]) + 0
Proud to be a Super User!
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |