Forum Discussion

Previsible's avatar
Previsible
Regular Visitor
1 year ago
Solved

Using a Measure to Combine Multiple Columns into one Card

Hello there,

 

I am attempting to build a dashboard that would require me to combine multiple columns of data to provide more detailed breakdowns of each section. 

Each column has only 3 expected outputs as this is stored in a sharepoint list, and the type of column is "Choice" meaning they can only choose "Yes" "No" or "NA"

For this report "Yes" and "NA" are considered a PASS and "No" is considered a FAIL.

 

To do this, I took the approach of making 3 Measurements:

 

ClientCountYesAndNASpecific = 
        SUMX(
        'Deskside QA',
        IF(
                'Deskside QA'[Didtheresourcefollow-upwit] IN {"Yes", "NA"} || 
                'Deskside QA'[Didthetechnicianpracticethe3Stri] IN {"Yes", "NA"},
            1,
            0
        )
    )
ClientTotalSpecificResponses = 
    SUMX(
        'Deskside QA',
        IF(
            NOT(ISBLANK('Deskside QA'[Didtheresourcefollow-upwit])) || 
            NOT(ISBLANK('Deskside QA'[Didthetechnicianpracticethe3Stri])),
            1,
            0
        )
    )
ClientPercentage = DIVIDE([ClientCountYesAndNASpecific], [ClientTotalSpecificResponses], 0) * 100

 

In theory, ClientCountYesAndNASpecific would only count "Yes" and "NA" responses, ClientTotalSpecificResponses would count all rows, and ClientPercentage would calculate the percentage of "Yes" and "NA" in comparison to "No"

 

When I put this into practice however I get unexpected results:

For example:
These 2 tables are the Column Source Counts:

165 lines total 
No = 16
All Lines =165
Meaning the expected output is 90.3%

Instead, this is what I see:


Does anyone know where I'm messing up on this? I have tried a few different ways around this but end up right back where I started. 

  • For anyone who comes across this post seeking the answer, it was provided by a reddit user.

     

    Instead of the OR Statement in my DAX || I needed to use an AND statement &&

    IE:

    ClientTotalSpecificResponses = 
        SUMX(
            'Deskside QA',
            IF(
                NOT(ISBLANK('Deskside QA'[Didtheresourcefollow-upwit])) &&
                NOT(ISBLANK('Deskside QA'[Didthetechnicianpracticethe3Stri])),
                1,
                0
            )
        )

4 Replies

  • Hi Previsible 

    Before getting into specifics of the DAX, just checking the logic.

     

    The current ClientCountYesAndNASpecific measure effectively counts rows where this condition is true:

    'Deskside QA'[Didtheresourcefollow-upwit] IN {"Yes", "NA"}
    || 'Deskside QA'[Didthetechnicianpracticethe3Stri] IN {"Yes", "NA"}

    i.e. either of the columns  Didtheresourcefollow-upwit or Didthetechnicianpracticethe3Stri is equal to "Yes" or "NA".

     

    But from your screenshot, Didthetechnicianpracticethe3Stri = "NA" on all 165 rows, so this condition is true for all 165 rows.

     

    Is this what you intended?

     

    From your example at the end, the expected result of 149/165 = 90.3% looks to be based on the values in column Didtheresourcefollow-upwit alone.

     

    Regards

    • Previsible's avatar
      Previsible
      Regular Visitor

      That is what I intended. The Column Didthetechnicianpracticethe3Stri is supposed to have all answers NA, this is a new metric to this report and all historical entries received a "pass" by being marked as NA. The hope would be however that ClientPercentage pulls information from both columns as opposed to the one alone.

      In testing your theory that it is only pulling the data from one list I added some "NA" and "Yes" data to the SharePoint list and it still shows 100%:

       

      • OwenAuger's avatar
        OwenAuger
        Super User

        It looks like the discussion has moved to Reddit so I'll let the it continue there.

  • Previsible's avatar
    Previsible
    Regular Visitor

    For anyone who comes across this post seeking the answer, it was provided by a reddit user.

     

    Instead of the OR Statement in my DAX || I needed to use an AND statement &&

    IE:

    ClientTotalSpecificResponses = 
        SUMX(
            'Deskside QA',
            IF(
                NOT(ISBLANK('Deskside QA'[Didtheresourcefollow-upwit])) &&
                NOT(ISBLANK('Deskside QA'[Didthetechnicianpracticethe3Stri])),
                1,
                0
            )
        )