Forum Discussion
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
Creating a concat of Region2 and 3 in all tables and make the relation through them would help?
7 Replies
- selimovd
Most Valuable Professional
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicThanks 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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
Creating a concat of Region2 and 3 in all tables and make the relation through them would help?
