Using CALCULATE, COUNT, FILTER and USERRELATIONSHIP

[DataMinion]

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 called Register that shows all new business submitted and also when it is in force, or ‘On Risk’, there are columns called ‘Submitted Date’, and another called ‘Date On Risk / NTU’ to show the respective dates. I want to be able to count the number of new business applications that are On Risk and not count ones that are NTU so that is why I’m using a filter.

I currently link the table to two separate date tables so that I can identify the Submitted Date and Date on Risk, but I want to just use one Date table called ‘Dates’ and incorporate the function USERRELATIONSHIP.

I have setup two expressions called ‘L12M’ and ‘ReportDate’ which work so that is not the issue.

My current formula that works when I use two date tables is

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

I have created the active relationship between Dates[Date] – Register[Submitted Date] and the inactive relationship between Dates[Date] – Register[Date on Risk / NTU]

My formula I’m trying to use is as follows

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

The Register will look like this

Client Name	Submitted Date	On Risk / NTU	Date on Risk / NTU
J Smith	01/01/2022	On Risk	15/01/2022
B Brown	01/01/2022	NTU	15/01/2022

I’m not getting any error messages, but it is not returning any value, using the above table it should return the count of 1. So how can I use the USERRELATIONSHIP function so that I can just use one Date table please.

Any help would be appreciated.

[amitchandak · Accepted solution]

@DataMinion , refer active/current employee code in HR blog or on the file attached after signature

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[DataMinion]

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.