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.
Solved! Go to Solution.
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:
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.
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:
sum ( cases[cases] ),
cases[Region - Level 1]
= SELECTEDVALUE ( 'Territory'[Region - Level 1] )
cases[Region - Level 2]
= SELECTEDVALUE ( 'Territory'[Region - Level 2] )
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?
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?
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates