Problem with Counting rows

[exe_binary]

Hello foks,
I have a data like

call_no	part_no	date_lt	country
1234567	aa123	5/15/2023	le
1234567	bb456	5/21/2023	le
1234567	cc789	5/26/2023	le
456789	qq987	4/12/2024	ks
456789	ww654	4/16/2024	ks
456789	xx258	5/5/2024	ks
987654	pp123123	7/7/2024	al
987654	oo999888	7/15/2024	al
885522	ff654321	8/25/2024	bg
885522	gg15915	9/15/2024	bg

Now, I need two calculations:
1. Max Date based on call_no but when I filter some specific part_no then I need Max Date to be also on level call_no but for that specific part_no. In other words, e.g. if I select call_no = 1234567 my MaxDate should be =  5/26/2023. But if I select part_no = aa123 and bb456 then my MaxDate needs to be = 5/21/2023.
2. Flag count calculation that will calculate my rows where 'date_lt' = MaxDate and DynamicLastRecordFlag = 1
Now, everything works fine in a Table viz, but in Matrix it doesn't work.

From the picture below, for call_no = 456789 I need to show only May value since it is the MaxDate. Also stands for call_no = 885522 where I need only the value from Septemebr.
Can you assist to fix this? 😄

Link for the .pbix file
Flag Count.pbix

[v-xingshen-msft]

Hi ALL,
Firstly SachinNandanwar  and Kedar_Pande  thank you for your solutions!
And @exe_binary ,We can change the way to achieve your needs, your original DAX statement can be affected by the context of the columns in the matrix, he will determine the maximum value of each month and return, the following is the use of a measure of a way to achieve Hope you can help!

Max_Value_Measure = 
CALCULATE (
    COUNTROWS(Sheet1),  
    FILTER (
        Sheet1,
        Sheet1[date_lt] = CALCULATE (
            MAX(Sheet1[date_lt]),  
            ALLEXCEPT(Sheet1, Sheet1[call_no]) 
        )
    )
)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[SachinNandanwar]

"From the picture below, for call_no = 456789 I need to show only May value since it is the MaxDate. Also stands for call_no = 885522 where I need only the value from Septemebr."

Based on the above description is this what you are looking for ?

Create a calculated column for Ranking the rows

Rank_Column = RANKX (
    FILTER (
        Sheet1,
       'Sheet1'[call_no] = EARLIER (Sheet1[call_no] )
    ),
    Sheet1[date_lt].[Date],
    , 
    Desc,Dense
)

And then create this measure

Max_Value = CALCULATE(COUNTROWS(VALUES(Sheet1[call_no])),Sheet1[Rank_Column]=1)

[exe_binary]

It works fine until I filter some specific part_no and here is the catch. If I filter some part_no, like in the picture below, call_no(SR) = 1234567 and part_no = aa123, bb123 - then my Max Date needs to be 7/4/2023 and Max_Value needs to be 1 here. 
In other words this Rank should be dynamic and to change based on applied filters.

This is how the result should look for the example above

SR	Date	Part No	Country	Max Date	Max Value
1234567	7/1/2023	aa123	US	7/4/2023	0
1234567	7/4/2023	bb123	US	7/4/2023	1

[v-xingshen-msft]

Hi @exe_binary ,
I'm glad to tell you that your original code is able to fulfill your needs, but it's only the context filtering that requires you to adjust the DAX. The allselected you used is for part_no, but in the matrix, the only row you selected is call_no, and it's subjected to the context filtering of date_it, which is what causes the problem to occur in both September and August when you filter. The problem occurs in both September and August, here's what we've tried, hope this helps!

Distinct Count of call_no = 
IF (
[DynamicLastRecordFlag] = 1,
    CALCULATE(
        DISTINCTCOUNT(Sheet1[call_no]),
        REMOVEFILTERS('Sheet1'[date_lt]),FILTER('Sheet1','Sheet1'[DynamicLastRecordFlag]=1)),
   BLANK()
)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[exe_binary]

@v-xingshen-msft  Thank, this works fine until I have 'call_no' in the view. If I would like to see how many 'call_no' with Rank = 1 I have per month, then I lost months from the Table in which I have several 'call_no'/'sr'.
Example with call_no in the view (I added additional 'call_no'/'sr' in October 2024)

Now, as you can see from the picture I have two 'call_no'/'sr' in October 2024
If I remove 'call_no'/'sr' from the table/view, I will lost October's data

So, in one month I can have several 'call_no' with Rank = 1 and if I want to SUM up to see total numbers of 'call_no'/'sr' per month I will lose those if I remove 'call_no'/'sr' from the Table/view.

[exe_binary]

What's happend with call_no = 1234567 since it is not in the table now?
Next, when I try to create RANK based on your instruction I got an error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

[SachinNandanwar]

1234567 is in year 2023 and my screenshot was of the data for 2024.

Here is the screenshot for both the years.

It is RANKX and not RANK. Create a calculated column and not a measure.

[Kedar_Pande]

MaxDate =
VAR SelectedCallNo = SELECTEDVALUE('YourTable'[call_no]) 
VAR SelectedPartNo = SELECTEDVALUE('YourTable'[part_no])
RETURN
CALCULATE(
MAX('YourTable'[date_lt]),
FILTER(
'YourTable',
(ISBLANK(SelectedPartNo) || 'YourTable'[part_no] = SelectedPartNo)
&& 'YourTable'[call_no] = IF(ISBLANK(SelectedCallNo), 'YourTable'[call_no], SelectedCallNo)
)
)

FlagCount =
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[date_lt] = [MaxDate],
'YourTable'[DynamicLastRecordFlag] = 1
)

If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn

[exe_binary]

@Kedar_Pandefor 'FlagCount' it returns me an error: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[Kedar_Pande]

Modified:
FlagCount =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[date_lt] = [MaxDate] &&
'YourTable'[DynamicLastRecordFlag] = 1
)
)

[exe_binary]

Still the same problem, but your FlagCount doesn't work well neither in Table not Matrix.
If you can see from the image below

'DynamicLastRecord' works fine in a Table and I need to figure out how to distinct count it and add that measure in Matrix, so for call_no = 456789 is should return just 1 in May. I do not need SUM. Not sure if you understand me.