Multiple response and filters

[darpao]

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

[ERD]

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.

[darpao]

Thank you @ERD ,

I still get all 1 in the table, like dividing the same amount.

Did you forget the second argument of calculate function in tech_id variable, maybe?

Can you attach the PBIX attemps that you did?

[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.

[darpao · Accepted solution]

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_id

RETURN

COALESCE(percentage,0)