Forum Discussion

exe_binary's avatar
exe_binary
Frequent Visitor
1 year ago

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 ...
DAX
SachinNandanwar's avatar
SachinNandanwar
Impactful Individual
1 year ago

"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's avatar
exe_binary
Frequent Visitor
1 year ago

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's avatar
    SachinNandanwar
    Impactful Individual
    1 year ago

    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.