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.
Hello,
I have a visual that I would like to show 0 instead of blanks for. I have done this before, but in this case it is not working and I am not sure where I am going wrong.
This is the visual in question:
I have created this same thing in another report, and this is the visual where I achieved displaying 0 instead of blanks:
Here are my measures for each of the visuals:
MEASURE WORKING = CALCULATE(COUNTX('Table', [ID]), FILTER('Table', [Group1] = [Group1]), FILTER('Table', [Group2] = "Value")) + 0
MEASURE NOT WORKING = CALCULATE(COUNTX('Table',[ID]), FILTER('Table', [Group1] = [Group1]), FILTER('Table', [Group2] = [Group2])) + 0
Both of these visuals have only one data table in them, so relationships are not an issue. Both of these visuals have two levels as the rows and a date field as the column, so they should be the exact same just with different data behind them. The only difference is that in the measure when it works I am filtering for a specific value of Group2, but even when I put that condition in the measure that does not work, it still shows blank values instead of 0. I am not sure where things are going sideways on this process, so any help would be appreciated. Thank you in advance!
What you can try is defining your measure as a VAR, like this: VAR V_YourMeasure = [Your Measure]. Then, you can write a RETURN statement that will return 0 if the VAR is blank:
RETURN
IF(
NOT ISBLANK(V_YourMeasure),
V_YourMeasure,
0)
Hi @NJ81858
Please try this
If(IsBlank(CALCULATE(COUNTX('Table',[ID]), FILTER('Table', [Group1] = [Group1]), FILTER('Table', [Group2] = [Group2])) + 0),0,CALCULATE(COUNTX('Table',[ID]), FILTER('Table', [Group1] = [Group1]), FILTER('Table', [Group2] = [Group2])) + 0)
Hi,
try wrapping your measure in an IF(ISBLANK(CALCULATE FUNCTION),0,(CALCUALTE FUNCTION))
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
@NJ81858 It is quite possible you are running into an AutoExist issue. To help narrow down what is going on, try this:
MEASURE NOT WORKING =
VAR __Group1 = MAX('Table'[Group1])
VAR __Group2 = MAX('Table'[Group2])
VAR __Table = FILTER('Table', [Group1] = __Group1 && [Group2] = __Group2)
VAR __Result = COUNTX( __Table, [ID] ) + 0
RETURN
__Result
Unfortunately, that yielded the same result in my matrix. The only difference is in the totals:
@NJ81858 So, I mocked this up. See PBIX below signature. You are running into AutoExist. So the whole testing for ISBLANK isn't going to work. Any time you introduce filters on multiple columns from the same table, you have the potential of running afoul of AutoExist. In these situations AutoExist determines whether or not any rows fit the filter criteria. If not, it won't even attempt the calculation. To solve it, you have to introduce another table but that can lead to other problems as you'll see in the PBIX.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |