Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Managing Relationships between 2 Fact Tables

Hi,

 

I'm having some issues with relationship in my data model that I need some help with. I have the following set up:

 

  • One FACT table for Ads data
  • One FACT table for Conversions data
  • There's a Key set up linking both of these
  • I also have numberous DIM tables, all linking into the Ads Table, which then links out to the Conversions table on the Key

I'm expecting that if I filter using something from one of the DIM tables that it will filter the Ads Fact Table, which will then in turn filter the Conversions Fact table. But that's not happening.

 

I've tried changing the cross-filter direction from Single to Both but neither works.

 

Here's an example of what I'm seeing:

  • Ad Group Name comes from one of the DIM tables
  • Total Impresions and Total Clicks come from the Ads Fact table
  • Conversion ID and Total Conversions both come from the Conversions Fact table

You'll see there are some rows where there are no Conversions but a Conversion ID. I would expect this row not to appear. How can I correct this?

 

Another issue I'm having above, is that on the second Ad Group Name, there are no Total Conversions whatsoever. I would like Conversion ID to be blank for this row and for only one row to show. How can I do this along with resolving the issues in the relationship?

 

Thanks,

Mark

Need Help
Show and Tell

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    don't link your fact tables directly. Control them both from one or more common dimension tables.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Thanks for the reply from lbendlin , please allow me to provide another insight:

     

    Consider using DAX functions in measure to dynamically control filter context propagation between tables.

     

    This approach allows you to maintain a one-way relationship in the model while achieving the desired filtering behavior in the calculation.

    Total Conversions =
CALCULATE(
     DISTINCTCOUNT(Conversions[Conversion ID]),
     CROSSFILTER(Ads[Key], Conversions[Key], BOTH)
)

     

    For more information about CROSSFILTER, please see:

    Bi-directional relationship guidance - Power BI | Microsoft Learn

     

    Create a calculated column in your Conversions Fact table that checks for the presence of conversions and returns the Conversion ID accordingly.

    Adjusted Conversion ID = 
IF(
    ISBLANK([Total Conversions]),
    BLANK(),
    [Conversion ID]
)

     

    If you have any further questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      HI Anonymous ,

       

      I've tried linking all DIM tables to the Ad FACT table, then linking the Ad FACT table to the Conversions FACT table on the Key, then using your suggestion for Total Conversions. But the behaviour is the same.

       

      I also tried linking all DIM tables directly to each FACT table but it's not narrowing down the Conversion IDs.

       

      I've tried adding in a filter to the visual that looks at Row Count > 0, which does work but only where there are Conversions with an Ad Group. This means it removed any Ad Group that doesn't have Conversions. I tried your adjusted_conversion_id calculation, but it didn't work either. I was wondering whether I needed to add a blank or 'OTHER' field to my main DIM table, so that could be referenced as a row? Then the filtering would work.

       

      Would that work? Any suggestions on how I could approach this?

       

      Thanks,

      Mark