Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 2 tables: -
Now I need to calculate 2 things i.e 2 calculated measures: -
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
Solved! Go to Solution.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |