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
Marfield's avatar
Marfield
Icon for Helper I rankHelper I
6 years ago

This is only an example to explain my problem/need.

My real data model contains about 20 dim tables and 3 fact table soooo... I doubt this would be efficient if I merged everything in only one table ğŸ˜‹

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.

         

         

    • 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.