Forum Discussion

CAPEconsulting's avatar
CAPEconsulting
Helper III
9 years ago
Solved

COUNTIFS across multiple tables

I have 2 tables: - 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/...
  • 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

     

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