Forum Discussion

MichaelC18's avatar
MichaelC18
Regular Visitor
2 years ago

Multiple tables adding numeric number once per table

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...
Calculated Measures
MargusMartsepp's avatar
MargusMartsepp
New Member
2 years ago

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:

  1. SUMMARIZE: This function is used to create a table with unique PersonID for each table.
  2. COUNTROWS: Counts the number of rows in the summarized table, effectively counting each individual once.
  3. CALCULATE: Modifies the filter context to include only rows where "EWS Score = 1."
  4. The VAR statements calculate the counts for each table separately.
  5. 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.

MichaelC18's avatar
MichaelC18
Regular Visitor
2 years ago

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
  • MargusMartsepp's avatar
    MargusMartsepp
    New Member
    2 years ago

    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

     

    • MichaelC18's avatar
      MichaelC18
      Regular Visitor
      2 years ago

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