Forum Discussion

Victor_AguirreV's avatar
Victor_AguirreV
Regular Visitor
2 years ago
Solved

Issues with Relationships and Calculate function

Hello guys!

 

I have the following DataModel:

 

 

And what I am trying to do is get the total number of chargebacks, calculated by counting the primary key in All_CBs table called "Journal ID". Then I need that count, divided by the total deposits in All_dep_bd, which is also a count on the primary key there called "Journal ID" as well.

 

The relationships are:

 

  • All_dep_bd (which has the deposits) is related to the Calendar table by the Date field in a many to one. This relationship is inactive
  • All_CBs is related to the Calendar table by the Date field in a many to one. This relationship is inactive
  • All_CBs and All_dep_bd are related to a Dim table called Player List, by "Player" field. 
  • Player List is related to the Calendar table by the Date field in a many to one relationship. This relationship is active.

 

This results in the following:

 

 

The table to the left has the date from the calendar table. The table to the right, has the date field from the All_CBs table.

 

The measure is the following:

 

Total CBs = CALCULATE(
        COUNTROWS(All_CBs),
        FILTER(All_CBs,All_CBs[IsSuperAFF]="Yes")
    )
 
the IsSuperAff field, checks whether the customer falls into the marketing group we are trying to use the calculation on.
 
Why is it that when I use the date from the Calendar table, I get different results, but when I use the date from the actual CB table, I get different numbers?
 
 
 

 

Need Help
  • Victor_AguirreV's avatar
    Victor_AguirreV
    2 years ago

    Hello Guys,

     

    I solved it. My apologies if someone was trying to figure this out. This was the solution:

     

    Total CBs by CB Date = CALCULATE([Total CBs],
        USERELATIONSHIP('Calendar'[Date], All_CBs[Date Chargeback])
    )
     
    Total CC Deposits by Date = CALCULATE([Total CC Deposits],USERELATIONSHIP(All_dep_bd[Short.Date],'Calendar'[Date]))
     
    CB Rate = DIVIDE([Total CBs by CB Date], [Total CC Deposits by Date])

     

1 Reply

  • Victor_AguirreV's avatar
    Victor_AguirreV
    Regular Visitor
    2 years ago

    Hello Guys,

     

    I solved it. My apologies if someone was trying to figure this out. This was the solution:

     

    Total CBs by CB Date = CALCULATE([Total CBs],
        USERELATIONSHIP('Calendar'[Date], All_CBs[Date Chargeback])
    )
     
    Total CC Deposits by Date = CALCULATE([Total CC Deposits],USERELATIONSHIP(All_dep_bd[Short.Date],'Calendar'[Date]))
     
    CB Rate = DIVIDE([Total CBs by CB Date], [Total CC Deposits by Date])