topic Re: Calculated Measure percent not blank in DAX Commands and Tips

Calculated Measure percent not blank

Anonymous — Thu, 25 Mar 2021 02:12:19 GMT

Hello everyone,

I have a table, and I would first like to filter rows where a condition is satified, and then from the rows that remain, calculate the percent of IDs where either of two columns are NOT BLANK, over all IDs. This would be for a card display.

Table:

ID	MVA?	Insurance 1	Insurance 2
1	True		U
2	False	A
3	False
4	True	F
4	True	F
5	False
6	True
6	True
7	False	Y
8	True	J

The logic would be:

For rows where MVA? = True, whats the percentage of IDs that had a value in either 'Insurance 1' or 'Insurance 2'

Please note, my IDs repeat, so this is an added complication

In this example, 3 IDs have a value in Insurance 1 or Insurance 2, out of 4 IDs total where MVA?=TRUE

Any help appreciated as I am totally lost!!

Thank you for your time,

Denisse

Re: Calculated Measure percent not blank

amitchandak — Thu, 25 Mar 2021 04:57:34 GMT

Anonymous , Try a measure like

divide(coutrows(filter(Table, [MVA]= true() && (not(isblank([Insurance 1])) || not(isblank([Insurance 1]))))) ,coutrows(filter(Table, [MVA]= true())))

or if you do not want true below

divide(coutrows(filter(Table, [MVA]= true() && (not(isblank([Insurance 1])) || not(isblank([Insurance 1]))))) ,coutrows(Table))

Re: Calculated Measure percent not blank

Anonymous — Fri, 26 Mar 2021 21:19:25 GMT

edhans ,

Would you know how to address this? It is a continuation of the previous problem you helped me with, the last step before I can build a dashboard.

I only got as far as counting the unqiue values of my IDs that are MVA=True/Yes:

mvaC = CALCULATE(DISTINCTCOUNT('delete sample'[IncidentId]), FILTER('delete sample', 'delete sample'[MVA]="Yes"))

I am having a hard time with coutining the rows that are not blank for insurance 1 or insurance 2

I appreciapte your help!!

Re: Calculated Measure percent not blank

edhans — Fri, 26 Mar 2021 22:06:01 GMT

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.

Re: Calculated Measure percent not blank

Anonymous — Fri, 26 Mar 2021 23:26:18 GMT

edhans

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:

Re: Calculated Measure percent not blank

edhans — Sat, 27 Mar 2021 00:03:04 GMT

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. 😁

Re: Calculated Measure percent not blank

Anonymous — Sat, 27 Mar 2021 00:32:29 GMT

edhans

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!

Re: Calculated Measure percent not blank

edhans — Sat, 27 Mar 2021 03:13:16 GMT

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.

Re: Calculated Measure percent not blank

Anonymous — Mon, 29 Mar 2021 18:08:51 GMT

edhans thank you for your time and help, Ed! It worked 🙏

Re: Calculated Measure percent not blank

edhans — Mon, 29 Mar 2021 18:59:36 GMT

Great Anonymous ! Glad I was able to assist, and hopefully show that using FILTER with ALL on a column(s) is fine. It is on a table that it can hit performance issues and is generally not a good practice.