topic Multiple tables adding numeric number once per table in DAX Commands and Tips

Multiple tables adding numeric number once per table

MichaelC18 — Tue, 12 Sep 2023 16:23:11 GMT

Hello, I have a question I just cant figure out. I have my powerbi with a field I named EWS Score in each table. I have my calculated field working across all tables and my measure. However, having 4 tables representing categories, how would I add this up so it counts each person once per table where the value would be 1 to 4? I need to see all places these individuals have two or more categories ( each category is its own table.) As in example if I were in table 1 8 times and table two zero times and table three 2 times and table four 1 time, my count would be 3. I would then apply a slicer so the users can control their count visuals. I am fairly new so an example goes a long way currently.

4 tables - ( Table_A, Table_B, Table_C, Table_D) -

count only if EWS Score =1 and count once per table

(Would this stand anone or be placed in my table I where I put the outher two items as a measure and a calculated field?)

Re: Multiple tables adding numeric number once per table

MargusMartsepp — Tue, 12 Sep 2023 16:34:53 GMT

DAX Measure for counting individuals across tables

TotalUniqueCounts = VAR CountTableA = CALCULATE(COUNTROWS(SUMMARIZE(Table_A, Table_A[PersonID])), Table_A[EWS Score] = 1) VAR CountTableB = CALCULATE(COUNTROWS(SUMMARIZE(Table_B, Table_B[PersonID])), Table_B[EWS Score] = 1) VAR CountTableC = CALCULATE(COUNTROWS(SUMMARIZE(Table_C, Table_C[PersonID])), Table_C[EWS Score] = 1) VAR CountTableD = CALCULATE(COUNTROWS(SUMMARIZE(Table_D, Table_D[PersonID])), Table_D[EWS Score] = 1) RETURN CountTableA + CountTableB + CountTableC + CountTableD

Explanation:

SUMMARIZE: This function is used to create a table with unique PersonID for each table.
COUNTROWS: Counts the number of rows in the summarized table, effectively counting each individual once.
CALCULATE: Modifies the filter context to include only rows where "EWS Score = 1."
The VAR statements calculate the counts for each table separately.
The RETURN statement sums these counts to give you the total count across all tables.

Style Guide Reference:
For DAX best practices, you can refer to the SQLBI DAX Coding Guidelines: SQLBI DAX Guidelines.

Re: Multiple tables adding numeric number once per table

MichaelC18 — Tue, 12 Sep 2023 17:06:36 GMT

I have tried it multiple times but I get an error. Did i misunderstannd an item. Those were genrric variables correct? You can leave those as they are?

TotalUniqueCounts =

VAR CountTableA = CALCULATE(COUNTROWS(SUMMARIZE('Attendance',Attendance[OTHER-ID])), Attendance[EWS Score]= 1)

VAR CountTableB = CALCULATE(COUNTROWS(SUMMARIZE('Discipline Datamining', 'Discipline Datamining'[Other ID])), 'Discipline Datamining'[EWS Score] = 1)

VAR CountTableC = CALCULATE(COUNTROWS(SUMMARIZE('EWS POSTED GRADES', 'EWS POSTED GRADES'[Stdt ID])), 'EWS POSTED GRADES'[EWS Score] = 1)

VAR CountTableD = CALCULATE(COUNTROWS(SUMMARIZE(TEST_SCORES_ELAMATLEVELS, TEST_SCORES_ELAMATLEVELS[Other ID])), TEST_SCORES_ELAMATLEVELS[EWS Score] = 1)

RETURN

CountTableA + CountTableB + CountTableC + CountTableD

Re: Multiple tables adding numeric number once per table

MargusMartsepp — Tue, 12 Sep 2023 17:11:27 GMT

Use FILTER with ALL to ensure that you're working with a valid table expression.

TotalUniqueCounts = VAR CountTableA = CALCULATE(COUNTROWS('Attendance'), FILTER(ALL('Attendance'[OTHER-ID]), 'Attendance'[EWS Score] = 1)) VAR CountTableB = CALCULATE(COUNTROWS('Discipline Datamining'), FILTER(ALL('Discipline Datamining'[Other ID]), 'Discipline Datamining'[EWS Score] = 1)) VAR CountTableC = CALCULATE(COUNTROWS('EWS POSTED GRADES'), FILTER(ALL('EWS POSTED GRADES'[Stdt ID]), 'EWS POSTED GRADES'[EWS Score] = 1)) VAR CountTableD = CALCULATE(COUNTROWS(TEST_SCORES_ELAMATLEVELS), FILTER(ALL(TEST_SCORES_ELAMATLEVELS[Other ID]), TEST_SCORES_ELAMATLEVELS[EWS Score] = 1)) RETURN CountTableA + CountTableB + CountTableC + CountTableD

Re: Multiple tables adding numeric number once per table

MichaelC18 — Tue, 12 Sep 2023 18:27:57 GMT

I still get an error. Not sure why this one seems to be fighting me.

Re: Multiple tables adding numeric number once per table

MichaelC18 — Mon, 18 Sep 2023 12:31:17 GMT

Does anyone have an answer for me for this report? I basically need the distince student to count once if on that report then add up for a sum of the four tables getting a score of 1 to 4. The current solutions are not running they errored out.

Re: Multiple tables adding numeric number once per table

MichaelC18 — Mon, 18 Sep 2023 14:52:31 GMT

I have the following up and running but my Attendance table has a score column. In Excel and SQL I can use a where clause (Where EWS Score =1) but in Dax how would I apply this to my attendance line only?TOTAL EWS POINTS =

( SUMX(

VALUES(Attendance[OTHER-ID]),

(CALCULATE(DISTINCTCOUNT(Attendance[OTHER-ID]))

)) + (SUMX(

VALUES('Discipline Datamining'[Other ID]),

CALCULATE(DISTINCTCOUNT('Discipline Datamining'[Other ID]))))

+ (SUMX(

VALUES('TEST_SCORES_ELAMATLEVELS'[Other ID]),

CALCULATE(DISTINCTCOUNT('TEST_SCORES_ELAMATLEVELS'[Other ID]))))

+ (SUMX(

VALUES('EWS POSTED GRADES'[Stdt ID]),

CALCULATE(DISTINCTCOUNT('EWS POSTED GRADES'[Stdt ID]))))

)