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

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

SRDatePart NoCountryMax DateMax Value
12345677/1/2023aa123US7/4/20230
12345677/4/2023bb123US7/4/20231
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      Anonymous  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.