cancel
Showing results for
Did you mean:
Helper I

## Calculate sum for filter related rows

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! 😉

1 ACCEPTED SOLUTION
Super User

@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/

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
2 REPLIES 2
Super User

@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/

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Helper I

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!