Forum Discussion

phemson4top's avatar
phemson4top
Frequent Visitor
8 years ago
Solved

DATEDIFF error when used with multiple columns in CALCULATE()

Good day team,

In my design, I have two tables Fact_DO_PF and Dim_Date with the following 1:* relationships

Dim_Date[DateKey] : Fact_DO_PF[XDateKey]    (Active)

Dim_Date[DateKey] : Fact_DO_PF[LastWD_DateKey]  (InActive)

Dim_Date[DateKey] : Fact_DO_PF[LastDEP_DateKey]  (InActive)

 

I want to have a measure in my project that will count number of profile that did not perform any activity in the last 90 days and above based on the user selected date in the slicer, and below is my DAX:

 

NoTransaction =

CALCULATE(COUNT(Fact_DO_PF[AccountKey]),

DATEDIFF(
DATE(LEFT(Fact_DO_PF[LastDEP_DateKey],4),MID(Fact_DO_PF[LastDEP_DateKey],5,2),RIGHT(Fact_DO_PF[LastDEP_DateKey],2)),
DATE(LEFT(Fact_DO_PF[XDateKey],4),MID(Fact_DO_PF[XDateKey],5,2),RIGHT(Fact_DO_PF[XDateKey],2)),
DAY
)>=90
)

 

The DAX gives this error: The Expression Contains Multiple Columns, but only a single column can be used in a True/False Expression that i used as a filter expression.

 

 

The expression was successful when I changed the End_Date parameter of the DATEDIFF to TODAY(), but I want to use the user selected date as the End_Date parameter.

 

Kindly assist me with a better idea in achieving my goal/aim.

Thanks and regards,

Phemson

  • Hi team,

     

    I got the solution.

    I eventually added a calculated column to the Fact_DO_PF table. I used DateDiff in the calculated column to find different between the two dates in day with dax shown below:

     

    LastDEPInDays = DATEDIFF(DATE(LEFT(Fact_DO_PF[LastDEP_DateKey],4),MID(Fact_DO_PF[LastDEP_DateKey],5,2),RIGHT(Fact_DO_PF[LastDEP_DateKey],2)),RELATED(Dim_Date[dDate]),DAY)

     

    and I used the column to filter my measure.

     

    NoTransaction = CALCULATE(COUNT(Fact_DO_PF[AccountKey]),Fact_DO_PF[LastDEPInDays]>90)

     

    Thanks to all

1 Reply

  • phemson4top's avatar
    phemson4top
    Frequent Visitor

    Hi team,

     

    I got the solution.

    I eventually added a calculated column to the Fact_DO_PF table. I used DateDiff in the calculated column to find different between the two dates in day with dax shown below:

     

    LastDEPInDays = DATEDIFF(DATE(LEFT(Fact_DO_PF[LastDEP_DateKey],4),MID(Fact_DO_PF[LastDEP_DateKey],5,2),RIGHT(Fact_DO_PF[LastDEP_DateKey],2)),RELATED(Dim_Date[dDate]),DAY)

     

    and I used the column to filter my measure.

     

    NoTransaction = CALCULATE(COUNT(Fact_DO_PF[AccountKey]),Fact_DO_PF[LastDEPInDays]>90)

     

    Thanks to all