Forum Discussion
Calculated Measure percent not blank
- 5 years ago
You need to upgrade to the March 2021 version of Power BI Desktop. It supports multiple columns now.
If you cannot, use this measure:CALCULATE( COUNTROWS( 'Table' ), FILTER( ALL( 'Table'[MVA?], 'Table'[Insurance 1], 'Table'[Insurance 2] ), 'Table'[MVA?] = TRUE() && ( 'Table'[Insurance 1] ) <> BLANK() || 'Table'[Insurance 2] <> BLANK() ) )But you should be on March 2021 if possible. Only do the above if your IT overlords are evil, or you are on Report Server, which will get this feature in May I think. 😁
Kinda walking in on the middle of this Anonymous but see if this helps. First of all, what is a "blank?" The data you gave had spaces, but that is understandable, tables in this forum cannot understand the difference between spaces, empty fields "", or nulls.
So, I forced the empty things to be null - a special value that will compare against BLANK() in DAX. This is what it looks like in Power Query. Note the word null for the blank areas.
Here is the M code. You might have to replace " " (there is a space there) or "" with null. When doing "", just leave the first box blank in the Replace Values box. Then use the lower case word null in the 2nd box.
This is tjhe M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQopKk0FUgpAHKoUqxOtZARkuSXmFINEHcEyIFFjJFEFuKgJwgA3/IKmWPWboToAj6A5kv5IuKgFQqkXRDAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"MVA?" = _t, #"Insurance 1" = _t, #"Insurance 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"MVA?", type logical}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Insurance 1", "Insurance 2"})
in
#"Replaced Value"
Now, for a card, the below DAX will return 4. In another visual, you may have filter context to deal with.
CALCULATE(
COUNTROWS( 'Table' ),
'Table'[MVA?]
= TRUE()
&& ( 'Table'[Insurance 1] )
<> BLANK()
|| 'Table'[Insurance 2]
<> BLANK()
)
Visually in the DAX model, you cannot tell if Insurance 1 has a space, is empty, often represented by "", or is null. So use Power Query to validate what is there, then adjust your DAX accordingly. Nulls are easier to deal with because you know what they are.
Also note that the MVA field for me is a real true/false value, not TRUE/FALSE as text. Adjust the DAX accordingly.
If that doesn't answer your question, please tell me what the answer should be and how I should arrive at it.
Thank you for looking into this for me, I appreciate it. I changed the blanks to nulls, and the MVA column to type boolean, and tried your meausre, but I get an error regarding having multiple columns:
- edhans5 years ago
Community Champion
You need to upgrade to the March 2021 version of Power BI Desktop. It supports multiple columns now.
If you cannot, use this measure:CALCULATE( COUNTROWS( 'Table' ), FILTER( ALL( 'Table'[MVA?], 'Table'[Insurance 1], 'Table'[Insurance 2] ), 'Table'[MVA?] = TRUE() && ( 'Table'[Insurance 1] ) <> BLANK() || 'Table'[Insurance 2] <> BLANK() ) )But you should be on March 2021 if possible. Only do the above if your IT overlords are evil, or you are on Report Server, which will get this feature in May I think. 😁
- Anonymous5 years agoNot applicable
hehe the IT overlords are not so kind, I'll have to wait until Monday.
I did a work around, since the two columns were complicating things, and I read that using filter ALL can get you in trouble later. I created a new boolean column named 'Insurance Blank' that tells me whether both insurance 1 and insurance 2 are blank. Then I managed to calculate the number of rows that are not blank for MVA=true (in image it's NumInsur), and the distinct rows when MVA=True (in image it's Count_MVA), but now I don't know how to combine everything to divide and get the percentage:
Thank you once again for your time!
- edhans5 years ago
Community Champion
I'm not sure what you are trying to do, but FILTER with ALL on a column is just fine. In fact, when you type:
CALCULATE( Expression, Table[Field] = 1 )DAX is doing this in the background:
CALCULATE( Expression FILTER( ALL(Table[Field]), Table[Field] = 1 ) )When I gave you the FILTER with ALL() in the 3 columns, that is the same thing - the exact same thing as the Calculate above with 3 conditions - and that is the point - they are't conditions at all, they are all tables! The predicate Table[Field] = 1 is converted to the FILTER() function by DAX to do the work. It is just syntax sugar to make typing the filters easier.
Doesn't mean what I gave you will work for your situation, but there is nothing wrong with what I did. Using FILTER with ALL for a table is what can cause performance issues, but I filtered columns, not a table.
The March 2021 update just enhances the syntax sugar to allow 2+ columns to be referenced without having to resort to using FILTER with ALL for 3 columns, but performance is the same, because DAX is really creating the larger measure I gave you to do the work.