DRIVING CRAZY: Measure involving two table variables selected through dynamic slicers.

[u92690]

Hey guys, 

I'm struggling  to find a solution. I have a ratio%  which is cases/tests. Both cases and tests are in different tables, but both have the same territory variables. I need to create a measure (cases/tests) with slicers the 3 region levels, so that i can choose any combination and i can see the ration. For instance, if i choose region 1 in the slicer  but levels 2 and 3 are not filtered, i want to show the ration for all the cases of region 1/ all the tests region 1. And so on. Is it very difficult?

I attach an example very simple for you to understand.

PBIX: https://drive.google.com/file/d/1ky3lIS8gjImwT5O4GJh3bheYkSMnMy5C/view?usp=sharing

tables: https://drive.google.com/file/d/1bKvRqhEv664ptYtmq3f6wPCfgpiCcqmG/view?usp=sharing

[u92690 · Accepted solution]

Creating a concat of Region2 and 3 in all tables and make the relation through them would help?

[selimovd]

@u92690 , yes that would also work. That's what I meant with fix your data 😉

[u92690]

How could I do it automatically in power BI? I don't know if whether choosing concatenate or concatenatex...

[selimovd]

Hey @u92690 ,

I would do it in Power Query already.

Just add a new column and combine the 2 columns with an underscore. This you can do easily with a "&" in Power Query:

Best regards

Denis

[selimovd]

Hey @u92690 ,

I tried to understand where you are struggling, but I'm not really sure if I got it.

I think your problem is that in the relationship you are connecting by Level 1, but the smallest granularity is Level 3.

Change the connection for both tables to Level 3 and see if that is what you were looking for:

By the way many-to-many relationships are dangerous. In your case so far I don't see a reason you need them. change them to 1:* from Territories to the two fact tables and always use the fields from the Territories table. Best remove Level 1 and Level 2 from the 2 fact tables.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[u92690]

Thanks for your answer. I had considered joining the tables through region 3. The thing is that not all region 3 are embedded in region 2. I mean, there can be some region 3 that are the same for one region 2. And on the other hand, some regions 3 for one region 2.  I tried the following code: 

Cases =
CALCULATE (
sum ( cases[cases] ),
FILTER (
cases,
cases[Region - Level 1]
= SELECTEDVALUE ( 'Territory'[Region - Level 1] )
),
FILTER (
cases,
cases[Region - Level 2]
= SELECTEDVALUE ( 'Territory'[Region - Level 2] )
),FILTER (
cases,
cases[Region - Level 3]
= SELECTEDVALUE ( 'Territory'[Region - Level 3] )
)
)

And it works fine, but i one filter in every slicer is requested. Otherwise, its not working. Any idea to sort it out?

Thanks

[selimovd]

Hey @u92690 ,

if the levels are not consistent and are changing I don't see an easy way to analyze that.

You could do connections between each level (L1 to L1, L2 to L2, L3 to L3), keep 2 of the connections inactive and if the slicer of Level 2 is active then use the Level-2 relationship and if the slicer of Level 1 is used then use Level 1 relationship.

But then you have to think how you make sure that the user is only using one slicer.

Isn't there a way to fix you data?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic