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)
