Forum Discussion

JD_'s avatar
JD_
Frequent Visitor
3 years ago

Datediff + USERELATIONSHIP Discrepancies

Hi everybody, 

 

I'm migrating a report from Tableau to PowerBI. This report includes several KPI and each KPI is related to a specific date.  That's why I have created a date table with inactive relationship for all the KPI dates (in order to use USERELATIONSHIP). 

 

All KPI Values are calculated by the same kind of formula : 

 

KPI Values = 
CALCULATE(
    CALCULATE(DISTINCTCOUNT(KPI) , KPI FILTER)
, USERELATIONSHIP(KPI DATE,'Date Table'[Date]))
 

But I have 2 issues with this way : 

 

1)

First, I was trying to calculate the days late for each KPI with a calculated column -> Dax formula = DATEDIFF(KPI TARGET DATE, KPI ACTUAL DATE, DAY). But when using USERELATIONSHIP, the days late contains values that it should not. 

Here a screen of the table I obtain. Only the values with a KPI should be counted but it's not the case.

And if I create the KPI without USERELATIONSHIP (and so without using the date table), I obtain the good results. 

I don't have enough experience to find by myself the problem even if I looked for. I can't find any interaction oreffect explaining this. But it's important to be resolved because I have to keep the date table in order to filter all the KPI at once. 

 

 

2)

My second issue is more global and weird. It is related with the count as such. Being a migration, I can compare my results with those in Tableau. 

Looking for the evolution of the KPI over time, there is a strange phenomenon where I obtain the good results for the first six months of the year but not since July. 

 

For example, for june I obtain 2471 values for a KPI both in PBI and Tableau. And same match for all the months before. But since July, I have some discrepancies between Tableau and PBI. (ex: 2756 for July in Tableau but 2715 in PBI).

I was wondering if someone here already had something like that or have an idea about the issue (even if I know it's quite vague) ?  

 

 

Thank you for your help, 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    JD_ , Userelationship change join between the table , this calculation filtering will change.

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • JD_'s avatar
      JD_
      Frequent Visitor
      3 years ago

      Thank you for answering. 

      amitchandak wrote:

      JD_, Userelationship change join between the table , this calculation filtering will change.

       

      What do you mean by that ?

      I understand the process of this function but I don't understand why it doesn't work for
      1) the days late : is-it a wrong interaction with calculated columns or something like that ? 

      2) values : why I have the good values in the first 6 months but not since July. I don't understand why there would  be a kind of delay. 

       

      FIY: It's for a restricted company, I can't share any data sorry 

       

      Thank you