topic Re: Using a Measure to Combine Multiple Columns into one Card in DAX Commands and Tips

Using a Measure to Combine Multiple Columns into one Card

Previsible — Mon, 06 Jan 2025 22:51:50 GMT

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.

Re: Using a Measure to Combine Multiple Columns into one Card

OwenAuger — Fri, 03 Jan 2025 22:26:43 GMT

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

Re: Using a Measure to Combine Multiple Columns into one Card

Previsible — Mon, 06 Jan 2025 20:57:11 GMT

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%:

Re: Using a Measure to Combine Multiple Columns into one Card

OwenAuger — Tue, 07 Jan 2025 01:32:11 GMT

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

Re: Using a Measure to Combine Multiple Columns into one Card

Previsible — Tue, 07 Jan 2025 17:12:11 GMT

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