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 | 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
11 Replies
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 LinkedInKedar_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.
Modified:
FlagCount =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[date_lt] = [MaxDate] &&
'YourTable'[DynamicLastRecordFlag] = 1
)
)
"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 rowsRank_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)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.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.
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 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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
