topic Re: Tricky count measure in DAX Commands and Tips

Tricky count measure

Jodallen123 — Thu, 20 Feb 2025 14:30:34 GMT

Hi,

I am trying to create a measure that counts the number of IDs per attribute, only the ID with the max date in the filtered period should be counted. This measure will be used in a matrix visual with attribute in the rows with ID under, and then a date (most likely week, but might be yearmonth) of some sort in the columns.

My data looks like this (notice that 102206 appears twice):

ID	Attribute	Days	Date
102206	Step1	23	2025-01-02
026174	Step2	413	2025-01-02
102071	Step2	1	2025-01-03
103311	Step2	3	2025-01-03
103513	Step2	3	2025-01-03
104152	Step3	78	2025-01-02
104350	Step3	70	2025-01-02
104393	Step3	65	2025-01-02
103650	Step3	86	2025-01-02
104299	Step3	70	2025-01-02
104097	Step3	71	2025-01-02
104233	Step3	69	2025-01-02
104474	Step3	59	2025-01-02
102772	Step3	88	2025-01-03
103651	Step3	94	2025-01-03
103899	Step3	92	2025-01-03
102206	Step3	15	2025-01-04
104108	Step3	68	2025-01-02
105270	Step4	17	2025-01-02
105231	Step4	20	2025-01-02
105216	Step4	21	2025-01-02
105285	Step4	15	2025-01-02

And the output should be like this:

Week	1
Step1	0
Step2	4
Step3	13
Step4	4
Total	21

Above is of course only one weeks worth of data, the actual report will probably be filtered to show the weeks in the current month.

I would very much appreciate if someone has some input or ideas how a measure like this can be created.

Have a nice day!

Re: Tricky count measure

quantumudit — Thu, 20 Feb 2025 20:30:30 GMT

Hello Jodallen123

Thank you for providing a sample dataset along with the results. To achieve the desired outcomes, you can create a DAX measure using the following formula:

ID Count = VAR _virtualTbl = ADDCOLUMNS ( 'Table', "@MaxDate", CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[Date] > EARLIER ( 'Table'[Date] ) ) ) ) RETURN CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( _virtualTbl, [@MaxDate] = BLANK () || [Date] = [@MaxDate] ) )

Make sure to change Table with the actual table name in your working Power BI file.

Here is the screenshot of the desired result in Power BI:

By default, "Step1" will not be displayed due to its blank or zero ID count. To make it visible, ensure that you check the "Show Items with no data". Below is a screenshot indicating where you can find this option:

Click on the "Attribute" in the Rows section and then, click on "Show items with no data"

I am attaching the Power BI file for your reference. Please review it and let me know if you have any further queries.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User

Re: Tricky count measure

Jodallen123 — Fri, 21 Feb 2025 08:40:08 GMT

Hi,

Thanks for the help!

I tried the measure that you posted on my full data-table and I could not get it to work. Then I made a copy of my main table with just one week filtered like what I posted above and then it seems to work, so it seems that something strange happens when there is more data. The output I get on my full data table looks like this:

Week	1
Step1
Step2	3
Step3	1
Step 4	4
Total	8

For example for step 2, it shows 3 since there are 3 IDs on the max date, but there is also one ID that should be shown from the day before. Step 4 is correct since all IDs end up on the same day. I am unsure how step3 has 1 since there are 13 in total (in my real data set there is no entry on 2025-01-04, I just added that to show that there can be more than one ID for one period). If it followed the logic of step 2 and 3 I think step3 should show a 3 since there are 3 IDs in step3 on the max date for that attribute (2025-01-03).

I would very much appreciate futher assistance!

Re: Tricky count measure

quantumudit — Fri, 21 Feb 2025 09:32:45 GMT

Hi Jodallen123

Could you please provide sample data that fully represents your issue or question in a usable format, rather than a screenshot? Ensure the dataset is anonymized and does not contain any sensitive or unrelated information.

Additionally, it would be very helpful if you could share the expected outcome based on the provided data - this can be in any format, including a screenshot. Having a clear reference for comparison will significantly improve the chances of getting the correct solution in the first response.

For best practices, you may find the following links useful:

Thanks,
Udit