Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated Measure percent not blank

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: 

IDMVA? Insurance 1Insurance 2
1True U
2FalseA 
3False  
4TrueF 
4TrueF 
5False  
6True  
6True  
7FalseY 
8TrueJ 

 

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

DAX Calculation
Measures
  • edhans's avatar
    edhans
    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. 😁

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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!! 
      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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.