How to create calculated column with count of distinct items per date & location?

Data_Scrubber — Thu, 15 Feb 2024 19:35:10 GMT

Hi,

I'm trying to add a calculated column to my dataset that counts the number of distinct serial numbers produced per day, and per site.

This value will be used as a global filter in the PBIX file, so it'll only show data for days where there are greater than X serial numbers.
It needs to be a filter, because different teams have different min values they want to use.

I have a decent amount of Power BI/DAX experience. This seems conceptually simple, but I'm WAY overthinking it, and that's led me down many PBI Community/stackoverflow rabbit holes 🙂 A nudge in the right direction would be greatly appreciated!

I think I need to do a group by with just the columns I want to aggregate on. This might be the right configuration for the groupby:

GROUPBY(Append2, Append2[Date], Append2[Site], "SN Count Per Day and Site", COUNTX(CURRENTGROUP(), DISTINCTCOUNT(Append2[Serial Number] )))

When I run that in the DAX Query View, it returns a table with 3 columns - date, site, SN count per day and site.

[Date]	[Site]	[SN Count Per Day and Site]
Thu Dec 01 2022	Site #2	3
Thu Dec 01 2022	Site #1	20
Fri Dec 02 2022	Site #1	15
Sat Dec 03 2022	Site #1	3
Tue Dec 06 2022	Site #2	13
Tue Dec 06 2022	Site #1	14
Wed Dec 07 2022	Site #2	15
Wed Dec 07 2022	Site #1	10
Thu Dec 08 2022	Site #1	8
Thu Dec 08 2022	Site #2	14

It's the values in the SN Count Per Day and Site column that I want to include in the new calculated column in my dataset. So for all twenty rows in the data set with a date of 12/1/2022 and site # of 1, they should all show the value 20.

Question #1: How do I do that? Is a JOIN the right approach? I've never done that before in DAX, but have in SQL. But that seems overly complex, so I think there's a concept I'm missing.

Question #2: For the GROUPBY - I'm confused on why I need to use COUNTX to aggregate here, but that's what appeared to work when I was playing around with the formula. The aggregation I want is the distinct # of serial numbers produced for that date/site combo. I believe COUNTX here would be the # of rows with a given date/site combo - which could mean duplicate serial numbers.

(I read the awesome FAQ before posting, and did my best on brevity)

Re: How to create calculated column with count of distinct items per date & location?

Rupak_bi — Thu, 15 Feb 2024 21:09:45 GMT

Try this syntax

Calculate(distinctcount ( table, sl no), allexcept( table, site, date))

Re: How to create calculated column with count of distinct items per date & location?

Data_Scrubber — Mon, 19 Feb 2024 19:36:28 GMT

Hi Rupak,

I greatly appreciate your prompt reply - sorry I was slow to respond.

That worked perfectly. I'm a little confused by how ALLEXCEPT works, but I think I just need to go process more examples for it to sink in.

THANK YOU!

topic Re: How to create calculated column with count of distinct items per date & location? in DAX Commands and Tips

How to create calculated column with count of distinct items per date & location?

Re: How to create calculated column with count of distinct items per date & location?

Re: How to create calculated column with count of distinct items per date & location?