Forum Discussion

Marfield's avatar
Marfield
Icon for Helper I rankHelper I
6 years ago

One table to rule them all

Hi there,   I have an issue with my data model. Bassicaly, I need to filter all my report according to the row filtered from one main table.   In my example, I have 3 dimensions and 1 fact table ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Why would that not be efficient? That is essentially what relationships are accomplishing. It would solve this issue 100%

VasTg's avatar
VasTg
Icon for Memorable Member rankMemorable Member
6 years ago

Anonymous 

 

Based on the model screenshot, you cannot make the relationship from source to client as active because of circular reference. Ideally, there should be only one active path, so that power bi will know which path to travese from Source to sales via Product. If all of them is active, it doesn't know it should traverse vis product or client. Thats the reason behind allowing only one relationship as active and rest as inactive(see USERELATIONSHIP in DAX).

 

If this helps, mark it as a solution

Kudos are nice too

 

  • Marfield's avatar
    Marfield
    Icon for Helper I rankHelper I
    6 years ago

    That's the main issue... I need to filter the dim tables according to the source selected.

    I understand this kind of relashionship shouldn't be allowed. However, according to the data I have in my model, this shouldn't be a problem and this would make sense. This is what I'm trying to achieve:

     

     

    Therefore, I've been trying to use RLS to create a filter in every dim table without creating the relashionships using the DimSource table. However, I didn't manage to do it...

    • VasTg's avatar
      VasTg
      Icon for Memorable Member rankMemorable Member
      6 years ago

      Marfield 

       

      Did you make the filter as bidirectional between sales to client and sales to Product? This should give the result you need. Test it out before merging Source to sales.

       

       

      • Marfield's avatar
        Marfield
        Icon for Helper I rankHelper I
        6 years ago

        Bi-directionnal wouldn't work because I have more than 1 fact table in my real model 😉

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    VasTg Exactly what I was getting at. I think you misunderstood who I was disagreeing with.

    • VasTg's avatar
      VasTg
      Icon for Memorable Member rankMemorable Member
      6 years ago

      Marfield 

       

      Try this...

      1. Create a bridge table(all combination of client,Product and source id)

      2. Define relationship from source to bridge(1 to M - single direction)

      3. Define relationship from Client  to bridge(1 to M - bidirection)

      4. Define relationship from Product to bridge(1 to M - bidirection)

      5. Join the bridge to facts(maybe M to M - single - bridge to facts)

       

      Hopefully this should work.

      Let us know.

       

      Anonymous 

      Sorry!!..Incorrectly tagged you in the previous post.

       

       

      • Marfield's avatar
        Marfield
        Icon for Helper I rankHelper I
        6 years ago

        This could do the trick in the example but it won't work because I would need one gigantic bridge table.

        My real model kinda looks like this (and will grow in size in a few weeks because there are still some tables and rows missing):

         

        Besides, I wouln't be able to see a table with ProductName, Fullname and Date. Which is kinda annoying for a fact table 😁