Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Creating a concat of Region2 and 3 in all tables and make the relation through them would help?
Creating a concat of Region2 and 3 in all tables and make the relation through them would help?
How could I do it automatically in power BI? I don't know if whether choosing concatenate or concatenatex...
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
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:
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |