Forum Discussion
Problem with Counting rows
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 ...
"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)
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 |