Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello! I've been trying to create a GROUPBY expression and one of the columns should be a DISTINCTCOUNT expression for the given group. However I get a message saying something like "CURRENTGROUP must be used...". The DISTINCTCOUNT funciton takes only column name.
So, how do I create a DISTINCTCOUNT column in a GROUPBY function?
Thanks!
Gorgi.
Solved! Go to Solution.
@Anonymous
If you want to "get distinct ItemId per area and then average that" then I would suggest something like this:
Average Distinct Items by Area = AVERAGEX ( VALUES ( Sheet1[AreaId] ), CALCULATE ( DISTINCTCOUNT ( Sheet1[ItemId] ) ) )
can you post sample data with expected result, it will help 🙂
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Here's an example: https://www.dropbox.com/s/aszcfyjm0r5zn7a/Example.pbix?dl=0
Check the measure. I want to include the DISTINCTCOUNT of AreaId column and average on it.
Thanks for your help @parry2k !
Hi,
Please explain the question is simple non technical language. What exactly are you trying to compute?
Hi @Ashish_Mathur thanks for your reply.
If you take a look at the Measure field from my example, I want to include a column as a result of the group by that would be a distinct count from ItemId. This is what I have tried:
Measure = AVERAGEX(GROUPBY(Sheet1;Sheet1[AreaId];"Sum";SUMX(CURRENTGROUP(); [Number]); "DistinctCount"; DISTINCTCOUNT(Sheet1[ItemId])); [Sum])
However, the error that I get is: "Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup()."
As you can see, one column of the GROUPBY result is "Sum" and uses SUMX with CURRENTGROUP and that works. However, I want a second column that will do DISTINCTCOUNT of ItemId for each AreaId.
In SQL I would write:
SELECT AreaId, SUM(Number), COUNT(DISTINCT ItemId)
FROM table
GROUP BY AreaId
I'm not sure if I can make it more clear then this 🙂
Hi @Anonymous,
As you can see, one column of the GROUPBY result is "Sum" and uses SUMX with CURRENTGROUP and that works. However, I want a second column that will do DISTINCTCOUNT of ItemId for each AreaId.
In SQL I would write:
SELECT AreaId, SUM(Number), COUNT(DISTINCT ItemId)
FROM table
GROUP BY AreaId
You can achieve with a table visual, just need to choose proper aggregation for each field.
Best regards,
Yuliana Gu
just wondering why you need to do group by, if you drop areaid, number and itemid, you can use sum aggregation for number and distinct count aggregation for itemid, and you will get the result.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for your reply @parry2k.
ItemId can be repeated for the same AreaId. I want to get distinct ItemId per area and then average that.
This is dummy data, but close to my real requirements.
The point is, I need to use DISTINCTCOUNT inside a GROUPBY expression and I can't figure out how.
@Anonymous
If you want to "get distinct ItemId per area and then average that" then I would suggest something like this:
Average Distinct Items by Area = AVERAGEX ( VALUES ( Sheet1[AreaId] ), CALCULATE ( DISTINCTCOUNT ( Sheet1[ItemId] ) ) )
Thanks @OwenAuger this will do the trick. But, it is still too weird to me that I cannot use DISTINCTCOUNT in a GROUPBY expression.
Thanks to everyone else for trying!
can you provide what you are expected to be output of sample data you shared in pbix
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
is this the output you are expecting from your sample data
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |