Distinct count of non-numeric value filtered by other table

[Roodakker]

Hi, 

I want to achieve a measure such as: Due by month = Distinct count of 'Efecte ID' montlhy by its 'next KYC date' 
so that I can use it when projecting/forecasting the upcoming workload by adding
[Amount of current work by month] + [Due by month]

When adding it into a matrix the numbers are adding up correctly, but since the 'next KYC date' is not the primary date  the measures won't work. Can anyone please help? 

This is what I want to achieve with my measure, but applied to a matrix. 

And here is the relationship between date and data tables: 

The table are connected to Calender (marked as date table) and to a second Calender table called "Next KYC" which is the one shown in the matrix above.
All datatypes are 'date'. 

Any ideas? 

/ Ronja 

[Anonymous · Accepted solution]

Hi @Roodakker ,
Based on your description, you would like to create a measure of non-repeating counts based on the "next KYC date".
Based on the data model relationship screenshot you provided, first you need to verify that the relationship between your "Next KYC Date" and the calendar table is set up correctly. It should be a one-to-many relationship, with the Next KYC Date being multi-party. This setting is critical for the TimeSmart feature to work properly.
You can use the userrelationship function:

Due by Month = 
CALCULATE(
    DISTINCTCOUNT('Kyc nordics'[Efecte ID]),
    USERELATIONSHIP('Kyc nordics'[next KYC date], 'CalendarTable'[Date])
)

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly