Forum Discussion

u92690's avatar
u92690
Frequent Visitor
5 years ago
Solved

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 ...
Need Help
  • u92690's avatar
    u92690
    5 years ago

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

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

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
 

 

  • u92690's avatar
    u92690
    Frequent Visitor
    5 years ago

    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's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      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