Forum Discussion
Multiple response and filters
I have a survey that generate 2 tables, one main table like this (Main):
| ID | Year | Country |
| 1 | 2018 | UK |
| 2 | 2019 | USA |
| 3 | 2019 | USA |
| 4 | 2020 | Germany |
| 5 | 2020 | USA |
| 6 | 2018 | Germany |
and another derived from a multiple response like this (Tecahnology):
| ID | Technology |
| 1 | A |
| 1 | B |
| 2 | B |
| 3 | A |
| 3 | C |
| 3 | D |
| 4 | C |
| 5 | B |
| 5 | C |
| 5 | D |
| 6 | A |
Now in a page I have 3 filters, one compulsory and another 2 optionals:
technology - must be A, B, C, or D (only one of this, single choice)
year - could be void or could be one or more out of 2018, 2019, 2020
country - like year, is not necessary to get it
and I want a summary table with this data:
Filter on A
| Year | Percentage |
| 2018 | =2/2 -> 100% because both 1 and 6 have technology A |
| 2019 | =1/2 -> 50% because only 3 has technology A and not 2 |
| 2020 | =0/2 -> 0% because neither 4 or 5 have technology A |
I'm not able to get the 2 of the denominator. I have tried CALCULATE with COUNTROWS, ALLSELECTED, ALLCROSSFILTER and so on, but nothing.
When then I filter also, for example country USA, the table should update like this:
Filter on A and USA
| Year | Percentage |
| 2018 | =0/0 -> 0% because both 1 and 6 are not in USA |
| 2019 | =1/2 -> 50% it remains like the previous table, because both 2 and 3 are in USA |
| 2020 | =0/1 -> 0% denominator change to 1 because only 5 is in USA |
Any helps?
I'm stucked with it from days.
Thank you
Finally I was able to find the solution.
Thank you ERD because your code helped me.
This is what I was looking for:
#Percentage = VAR main_id = CALCULATE(COUNTROWS('T-main'),REMOVEFILTERS('T-tech'[Technology]))VAR tech_id = COUNT('T-tech'[ID])VAR percentage = tech_id / main_idRETURNCOALESCE(percentage,0)
4 Replies
- ERD
Community Champion
Hi darpao ,
I suppose your tables are connected via ID column. You can try this measure:
#Percentage = VAR main_id = COUNT('T-main'[ID]) VAR tech_id = CALCULATE(COUNT('T-tech'[ID])) VAR percentage = tech_id / main_id RETURN COALESCE(percentage,0)If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
- ERD
darpao ,
I'm not sure what do you mean. You can check what you get by separating the measure:
#tech_id = CALCULATE(COUNT('T-tech'[ID]))#main_id = COUNT('T-main'[ID])If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
