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