Calculate sum for filter related rows

[addicted87]

hey @all,

its time for another question and chance to help me 😄

I've got following issue. I have 2 tables (without relationship, because it would be a m:n relationship).

table 1:

region	atc5	atc7	amount	price	value
germany	A01AB	A01AB01	10	20	200
usa	A01AB	A01AB01	5	15	75
japan	A01AB	A01AB01	20	10	200
germany	A01CC	A01CC05	5	5	25
usa	A01CC	A01CC05	7	6	42
usa	A01XX	A01XX03	10	10	100
germany	A02DD	A02DD01	5	5	25
usa	B05AA	B05AA40	10	10	100
usa	B05AA	B05AA50	1	1	1

table 2

atc7	atc5	icd
A01AB01	A01AB	E11
A01AB01	A01AB	E13
A01AB07	A01AB	E13
A01CC05	A01CC	E11
A01CC05	A01CC	G22
A01XX03	A01XX	G22
A01XX03	A01XX	G40
A02DD01	A02DD	E11

Use-Case:

I'd like to select A01AB in a slicer for table1[atc5]. This slicer is used on every single sheet. Now I want to know, which of the atc7 codes are related via table 2 atc5--> icd-->atc7.

Example:

A01AB relates to following icds ( yes I know, there is no relationship in the data model)

E11

E13

Now I want to find all atc7 codes, related to E11 or E13 (therefor I use table 2 as well)

A01AB01

A01AB07

A01CC05

A02DD01

How should the result looks like?

Well, I just need a straigt table (later a bar chart) with columns (atc7, sum(amount), sum(value)), but thats the problem. By selecting A01AB in the slicer, I will just get A01AB01 and A01AB07. How can I solve this?

Here is my current formula:

sum of amount related atc7 =
var vatc5 = VALUES(table1[atc5])
var vtable_icd = CALCULATETABLE(VALUES(table2[icd]);table2[atc5]=vatc5)
var vtable_atc7 = CALCULATETABLE(VALUES(table2[atc7]);table2[icd] IN vtable_icd)

return

CALCULATE(sum(table1[amount]];ALL(table1[atc5]);table1[atc7] IN vtable_atc7)

I get the sum of amount of the A01AB01 and A01AB07, but unfortunalety not for the other two ones.

Hope you got it and find a way to help me.

THANKS! 😉

[amitchandak · Accepted solution]

@addicted87 , not very clear. You need to try something like this

Measure =
var _act7 = summarize(allselected(Table1, Table1[Act7)) //because of AC5 and allselected should get ACT7
return
calculate(count(Table2[act]), filter(Table2, Table2[act7] in _act7))

or refer treatas

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[addicted87]

I know that this one is really strange. I try to explain it more detailed.

ATC5 is a code for a pharmaceutical ingredient and it stands for a group of more detailed subgroup of igredients. So one ATC5 has 1...n ATC7 Codes and is always of length = 5.

ATC 7 is a subgroup of ATC5, so it is more detailed. Its of lenght 7. Lets imagine, your head is aching. Your doctor prescibes you a drug thats makes you feel better. There is of course not just one drug against  headache. Every of this drugs have the same group of ingredients, but while product A got A01AB03, another one has A01AB05. There are several diseases, which are cured with this ingredients and only this one. So you could say, if a patient got a drug with A01AB he has got headache.

Now, there are several forms of headache. Each form has an icd. like E11 or E13. So I'm interessted in all forms of headaches and not the one, related to only A01AB.

Thats why I select a ATC5, which means, I select one particual form of headache. But I need now all the other forms of headches as well, thats why I'm looking on the icds and furthermore to all ATC7 (and of course ATC5 as well) which are related to headaches. Because there could (and there are) other pharmaceutical ingredients not equal A01AB, doctors prescribe when patients got headaches. More clearly now?

Of course I could do a  table1 left join table2 on atc5, but this way my table 1 would blow up so hard, because if there is one ATC5 in table1 and 3 ATC5, related via icd to the ATC5 of table1, i would get 3 instead of 1 rows in table 1.