Dax count rows for a group for the last date

[Anonymous]

Hi,

I want to count distinct Numbers column if the Event Code = CANCEL for last Entry Date Time Local  per Number.

for example, I want below Number to be counted as 1 because the Event Code = CANCEL  is the last Entry Date Time Local 

but below should not be counted because the Event Code = CANCEL  is not the last Entry Date Time Local :

can you please help.

[isabella]

Hi @Anonymous 

I did a local test ,you can take a reference ,here is my steps:

1.create a measure to calculate the max entry date per number

  max entry date per number = CALCULATE(MAX('Table'[Entry Date Time Local]),ALLEXCEPT('Table','Table'[Number]))

2.create a measure to sum the distinct number that satisfy the condition

distinct count number = SUMX('Table',IF([max entry date per number]=[Entry Date Time Local] && [Event Code]="CANCEL",1,0))

Best Regards,

Community Support Team _Isabella

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

[Anonymous]

Hi @Ashish_Mathur ,

I can't share the PBI file because I use tabular model as the data source.

I want to see the result in a matix by location or date.

[Ashish_Mathur]

Hi,

Do you want to see the result in a card visual?  Share the link from where i can download your PBI file.