Forum Discussion
How to DistinctCount between a row context and including two further filters?
Hi,
I have a Date table (dimDate). A basic mock-up is below:
dimDate[Date] dimDate[Year] dimDate[Month]
31/05/2020 2020 May
01/06/2020 2020 June
I have the Customer table. A basic mock-up is below:
Customer[Customer ID] Customer[Opening Date] Customer[Last Active] Customer[Type]
123 06/09/2019 blank Block
453 08/01/2024 19/01/2024 Circle
923 11/01/2024 02/02/2024 Circle
The dimDate[Date] column is a 1-* relationship to the customer[Opening Date] column.
I have a Matrix visual where the only two row values are the dimDate[Year] and dimDate[Month].
I need to be able to count the distinct Customer[Customer ID] records where the Customer[Opening Date] and the [Customer[Last Active] dates fall within the same Matrix period (row context?).
For example, if the Matrix's dimDate[Year] hasn't been expanded (so we're focusing on counts by dimDate[Year] of say 2019) then I need the DAX to calculate the number of unique Customer[Customer ID] records where that Customer ID has a Customer[Opening Date] within 2019 and the same Customer ID record having a Customer[Last Active] within 2019.
Should the Matrix's dimDate[Year] (i.e. 2019) been expanded to show the dimDate[Month] (so focusing on the month of July for this explanation) then I need to the DAX to calculate the the number of unique Customer[Customer ID] records where that Customer ID has a Customer[Opening Date] within July 2019 and the same Customer ID record having a Customer[Last Active] within July 2019.
As my mock-up dataset above shows, there may be Customer[Customer ID] records where the Customer[Opening Date] and Customer[Last Active] don't fall within the same period (i.e. either dimDate(Year] and dimDate[Month]) and so these should not be counted.
If grouping by dimDate[Year] only (so the dimDate[Month] hasn't been expanded), then you may have a Customer[Customer ID] record that has a Customer[Opening Date] of 05/09/2022 and a Customer[Last Active] of 12/12/2022. If so this would be included in the dimDate[Year] Matix row context as it's the same dimDate[Year], but wouldn't be included if the Matrix has been expanded to show the dimDate[Month] row context.
To add, there is another filter clause that needs to be present and that is simply Customer[Type] = "Block".
I hope this makes sense. Obviously, the same calculation needs to perform for each year and month.
How do I achieve this?
Thanks.
Hi D_PBI ,
Here I create a sample to have a test.
Please try this code to create a measure.
Measure = CALCULATE ( COUNT ( Customer[Customer ID] ), FILTER ( Customer, Customer[Last Active] <> BLANK () && Customer[Opening Date] >= MIN ( DimDate[Date] ) && Customer[Last Active] <= MAX ( DimDate[Date] ) && Customer[Type] = "Block" ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
if you compare scalar values to ranges you cannot use a data model. Instead use disconnected tables, INTERSECT and measures.
Hi D_PBI ,
Here I create a sample to have a test.
Please try this code to create a measure.
Measure = CALCULATE ( COUNT ( Customer[Customer ID] ), FILTER ( Customer, Customer[Last Active] <> BLANK () && Customer[Opening Date] >= MIN ( DimDate[Date] ) && Customer[Last Active] <= MAX ( DimDate[Date] ) && Customer[Type] = "Block" ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
