topic Re: Problem with Counting rows in DAX Commands and Tips

Problem with Counting rows

exe_binary — Wed, 09 Oct 2024 17:24:02 GMT

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

Re: Problem with Counting rows

Kedar_Pande — Wed, 09 Oct 2024 17:51:45 GMT

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 LinkedIn

Re: Problem with Counting rows

exe_binary — Wed, 09 Oct 2024 18:01:02 GMT

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

Re: Problem with Counting rows

Kedar_Pande — Wed, 09 Oct 2024 18:06:16 GMT

Modified:
FlagCount =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[date_lt] = [MaxDate] &&
'YourTable'[DynamicLastRecordFlag] = 1
)
)

Re: Problem with Counting rows

exe_binary — Wed, 09 Oct 2024 18:18:16 GMT

Still the same problem, but your FlagCount doesn't work well neither in Table not Matrix.
If you can see from the image below

'DynamicLastRecord' works fine in a Table and I need to figure out how to distinct count it and add that measure in Matrix, so for call_no = 456789 is should return just 1 in May. I do not need SUM. Not sure if you understand me.

Re: Problem with Counting rows

SachinNandanwar — Wed, 09 Oct 2024 20:55:52 GMT

"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)

Re: Problem with Counting rows

exe_binary — Wed, 09 Oct 2024 21:40:47 GMT

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.

Re: Problem with Counting rows

SachinNandanwar — Wed, 09 Oct 2024 22:12:53 GMT

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.

Re: Problem with Counting rows

Anonymous — Thu, 10 Oct 2024 05:26:27 GMT

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 Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Problem with Counting rows

exe_binary — Thu, 10 Oct 2024 08:21:34 GMT

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

Re: Problem with Counting rows

Anonymous — Tue, 15 Oct 2024 01:24:42 GMT

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.

Re: Problem with Counting rows

exe_binary — Tue, 15 Oct 2024 09:06:03 GMT

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.