cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
u92690
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
u92690
Frequent Visitor

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

View solution in original post

7 REPLIES 7
u92690
Frequent Visitor

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

View solution in original post

selimovd
Community Champion
Community Champion

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

u92690
Frequent Visitor

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

selimovd
Community Champion
Community Champion

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:

selimovd_1-1621069078356.png

 

Best regards

Denis

selimovd
Community Champion
Community Champion

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:

selimovd_1-1621006500928.png

 

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
 

 

u92690
Frequent Visitor

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
Community Champion
Community Champion

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
 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors