Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Model ambiguity workaround advice

Hi

 

I am running into the classic issue of not being able to create an active relationship between two tables, because it would introduce ambiguity to the model.

 

I've read a dozen or so questions on here, some suggesting to duplicate Tables... Create DAX expressions with the USERELATIONSHIP method... I can't help but feel like these are odd solutions.

 

My business logic is as follows:

 

Transaction / Merchant

  • A Transaction can only be redeemed at one Merchant.
  • A Merchant can redeem many Transactions.

Transaction / Date

  • A Transaction can be only be redeemed on one Date.
  • A Date can have many redeemed Transactions.

Merchant / Date

  • A Merchant can be registered on one Date.
  • A Date can have many registered Merchants.

 

A summarised model view:

The dotted line cannot be activated.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I am a little perplexed as to how to conveniently resolve this issue. I guess PowerBI is quite different from traditional database design. Any help is appreciated.

 

I should Note the ambiguity is between Date and Transaction.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Cross filtering both directions works well for a star shema like below.

     

    While cross filtering direction does not work well as below with loops.

     

    The above model can create an ambiguous set of relationships. For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table. For further information, you may refer to the document .

     

    If the relationships are all active In your model, when you sum up a field from 'Transcation' and then choose to filter by a field on Date. Then it is not clear how the filter should travel, through 'Date'=>'Transcation' or 'Date'=>'Merchant'=>'Transcation'. 

     

    You may make the relationship between 'Date' and 'Merchant' inactive. Then you can use USERELATIONSHIP to get the result according to the slicer.

    Here is a example about 'USERELATIONSHIP'. The pbix file is attached in the end.
    Table:


    Calendar:

     

    There are two inactive relationships between two tables.

     

    Then you can create measures to calculate the sum of 'Val' filter by 'Calendar[Date]' use filter 'CalendarDate'=>'Table[Date1]' or 'CalendarDate'=>'Table[Date2]'.

     

     

    Measure1 = 
CALCULATE(
    SUM('Table'[Val]),
    USERELATIONSHIP('Table'[Date1],'Calendar'[Date])
)
    Measure2 = 
CALCULATE(
    SUM('Table'[Val]),
    USERELATIONSHIP('Table'[Date2],'Calendar'[Date])
)

     

     

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , In this case as the Transaction table, is the fact. Remove the relation between Merchant and date or make it inactive.

     

    When you make that active using userelationship(merchant and date) also use crossfilter to remove relation between (transaction and date)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the response amitchandak .

       

      I can make the Merchant / Date relationship inactive, sure. Though, I'm a bit perplexed as to how implement the USERELATIONSHIP function.

       

      The scenario would be creating a Slicer, using 'Date'[Date], to filter 'Merchant'[Registration Date]. 

       

      Thanks

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    Cross filtering both directions works well for a star shema like below.

     

    While cross filtering direction does not work well as below with loops.

     

    The above model can create an ambiguous set of relationships. For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table. For further information, you may refer to the document .

     

    If the relationships are all active In your model, when you sum up a field from 'Transcation' and then choose to filter by a field on Date. Then it is not clear how the filter should travel, through 'Date'=>'Transcation' or 'Date'=>'Merchant'=>'Transcation'. 

     

    You may make the relationship between 'Date' and 'Merchant' inactive. Then you can use USERELATIONSHIP to get the result according to the slicer.

    Here is a example about 'USERELATIONSHIP'. The pbix file is attached in the end.
    Table:


    Calendar:

     

    There are two inactive relationships between two tables.

     

    Then you can create measures to calculate the sum of 'Val' filter by 'Calendar[Date]' use filter 'CalendarDate'=>'Table[Date1]' or 'CalendarDate'=>'Table[Date2]'.

     

     

    Measure1 = 
CALCULATE(
    SUM('Table'[Val]),
    USERELATIONSHIP('Table'[Date1],'Calendar'[Date])
)
    Measure2 = 
CALCULATE(
    SUM('Table'[Val]),
    USERELATIONSHIP('Table'[Date2],'Calendar'[Date])
)

     

     

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.