Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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)
Solved! Go to Solution.
Try this syntax
Calculate(distinctcount ( table, sl no), allexcept( table, site, date))
Try this syntax
Calculate(distinctcount ( table, sl no), allexcept( table, site, date))
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!
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |