Toronto Power BI, Power Apps, Power Automate User Group

Please login or click SIGN UP FOR FREE to create your PowerPlatformUG account to join this user group.
 View Only
Expand all | Collapse all

why a single-only existing relation between fact-table & dim-table is inactive --AdventureWorksDW

  • 1.  why a single-only existing relation between fact-table & dim-table is inactive --AdventureWorksDW

    Posted Oct 18, 2021 10:43 AM

    Hello There,

    I have a situation -- I noticed that the only exisiting-single relation between the (Fact-table) FactResellerSales and DimReseller (dimension table) is inactive. I happen to notice it as I am unable to filter FactResellerSales -- total-sales by a Reseller-column in DimResller-table -- as I get the same total across all the Resellers in the row.

     

    My concern is, what could the scenarios a relation could be inactive other than when there are multiple-relations between a given fact-table and dimension-table? 

     

    Would anyone please help me understand the scenario? Please find the screenshot of the model. Thank you for giving your valuable time.



    ------------------------------
    ArunaRamana Program
    student
    ------------------------------


  • 2.  RE: why a single-only existing relation between fact-table & dim-table is inactive --AdventureWorksDW

    Posted Mar 09, 2022 12:00 PM
    PowerBI will make a relationship inactive if it needs to - usually if there are more than 1 relationship between tables, but more generically; if there's any ambiguity in the relationship.  With role playing dimensions this happens - consider a fact table with Order Date and Ship Date.  If you intend to use only 1 calendar dimension, you would create 2 relationships, but only 1 can be "active".

    We can "activate" an inactive relationship for the purpose of a measure with the DAX statement "userelationship" - so in the previous example of order date and ship date, we can create two measures for quantity; ordered quantity and shipped quantity, one of which will need to use the "userelationship" dax function (whichever has the inactive relationship assigned to it).  Then we can use the same calendar dimension and graph orders and shipments on the same time series axis.

    Hope that helps!

    ------------------------------
    Ross Couldrey
    ------------------------------