Forum Discussion

CAPEconsulting's avatar
CAPEconsulting
Helper III
9 years ago
Solved

COUNTIFS across multiple tables

I have 2 tables: -

  1. Table 1 is called "Epi" that has a list of all clients as each rows and one of the columns is called "Current Distress" which has text options of "true", "false" and null values/blank. This table also has a column called "Geography" which has the suburb name for all clients
  2. Table 2 is called "State" which has all suburb names and their respective States with total population of each state

 

Now I need to calculate 2 things i.e 2 calculated measures: -

  1. the percentage of total clients by each state that have current distress i.e. either "true" or "false" for the Epi [Current Distress] column
  2. the percentage of total state population that has current distress

 

So basically in the excel world I would need for each suburb =SUM(COUNTIFS($M$2:$M$390000,"true",$N$2:$N$390000,"Suburb X"),COUNTIFS($M$2:$M$390000,"false",$N$2:$N$390000,"Suburb X")) and then SUMIF for the above to aggregate totals by state.

 

So how do I do this in POWER BI using DAX

  • chbraun's avatar
    chbraun
    9 years ago

    Hi,

     

    first thing you need to make sure is that the two tables have a relationship (i.e. connect them via the Geography attribute). Then you need a few simple measures:

     

    + count client in distress (CALCULATE(COUNTROWS(EpiTable), Status = "true") or something like that)

    + count all clients (DISTINCTCOUNT(Client)

    + state population (SUM(State!Population)

     

    Then you set up the measures for your percentages, making sure to use the ALL function in the denominator to get the total counts.

     

    You don't need to do anything to get counts per state - that will be taken care of automatically by Power BI via cross-filtering and context-setting; for example, if you set up a bar chart to show the numbers per state the above measures will be evaluated in the context of the state belonging to each bar.

     

    Hope this helps! :-)

     

    Christian

     

2 Replies

  • chbraun's avatar
    chbraun
    Helper I
    9 years ago

    Hi,

     

    first thing you need to make sure is that the two tables have a relationship (i.e. connect them via the Geography attribute). Then you need a few simple measures:

     

    + count client in distress (CALCULATE(COUNTROWS(EpiTable), Status = "true") or something like that)

    + count all clients (DISTINCTCOUNT(Client)

    + state population (SUM(State!Population)

     

    Then you set up the measures for your percentages, making sure to use the ALL function in the denominator to get the total counts.

     

    You don't need to do anything to get counts per state - that will be taken care of automatically by Power BI via cross-filtering and context-setting; for example, if you set up a bar chart to show the numbers per state the above measures will be evaluated in the context of the state belonging to each bar.

     

    Hope this helps! :-)

     

    Christian

     

    • CAPEconsulting's avatar
      CAPEconsulting
      Helper III
      9 years ago

      Many thanks @chbraunChristian for the advice.

       

      On another measure I am doing something similar and have 2 options

       

      Asthma Prev = CALCULATE(COUNT(Epi[asthma active]),Epi[asthma active] = "true")/COUNT(Epi[ID])

      or

      Asthma Prev = CALCULATE(COUNTROWS(Epi),Epi[asthma active] = "true")/COUNT(Epi[ID])

       

      Both give the same answer. Could you tell me the differences in the 2 apparoaches and which one to go for.

       

      Also Asthma Prev = CALCULATE(COUNTA(Epi[asthma active]),Epi[asthma active] = "true")/COUNT(Epi[ID]) is alsoe giving me the same answr. ALL 3 OPTIONS SEEMS TO WORK. Butnot sure which one is BEST