Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to build a dashboard showing how many of our records have missing fields. As part of this, I've got a measure to track records where at least one field is blank, looking like this:
MissingDataCount = calculate ( count ( [RECORD_NUMBER] ) , Field1 = blank() || Field2 = blank() || Field3 = blank()|| Field4 = blank() )
(spaces not included, this is just to help you read it)
This works fine and accurately. The problems comes when I try to create a table showing what MissingDataCount is split by one of the used in the formula fields, or I use a field slicer on it. When that happens, it fails to split the count by the field, and just shows the grand total each time. So for example, when I want this
Field1 Missing Data Count
A 10
B 20
C 30
I instead get this:
Field1 Missing Data Count
A 60
B 60
C 60
This is especially bad if there's only 20 records in A, for example, because then the Missing Data % is calculated to be 60/20, or 300%!
I'm assuming the problem is that the measure is being calculated before the slicers/tables go to work, or that I'm causing some form of circular dependency by filtering the field twice. My users will want to slice the data by the active field, and when that happens, it breaks and delivers an inaccurate result.. I'm wondering if there's any way around this, using dax or the options?
Solved! Go to Solution.
MissingDataCount = calculate ( count ( [RECORD_NUMBER] ) , FILTER( Table, Field1 = blank() || Field2 = blank() || Field3 = blank()|| Field4 = blank() ) )
Thanks for raising this. I have had the exact same issue.
Why do things go wrong when you apply the same filter used in the calculate function as a column in the table visual?
No idea why the 'FIlter' function added above resolves this mystery
MissingDataCount = calculate ( count ( [RECORD_NUMBER] ) , FILTER( Table, Field1 = blank() || Field2 = blank() || Field3 = blank()|| Field4 = blank() ) )
Damnit, of course it's that easy. Thanks a lot, really helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |