Forum Discussion

DataMinion's avatar
DataMinion
Helper I
3 years ago
Solved

Using CALCULATE, COUNT, FILTER and USERRELATIONSHIP

Hi,   I'm struggling to make this work using CALCULATE, COUNT, FILTER and USERRELATIONSHIP all in the same formula with Power Pivot so hopefully someone could help me please.   I have a table cal...
  • DataMinion's avatar
    DataMinion
    Helper I
    3 years ago

    Hi amitchandak,

     

    I referred to your solution and I got the formula to work. Your solution was really good as it used both USERRELATIONSHIP and CROSSFILTER to show a different way. You also used COUNTX so I changed my original formula around and got it to work.

     

    My formula is now

     

    CALCULATE(COUNTX(FILTER(Register, Register[On Risk / NTU] = "On Risk" && Register[Date on Risk / NTU] > [L12M] && Register[Date on Risk / NTU] <= [ReportDate]), (Register[On Risk / NTU])),
    USERRELATIONSHIP(Register[Date on Risk / NTU], Dates[Date]))

     

    Thank you for your help.