Use DISTINCTCOUNT in a GROUPBY expression

[Anonymous]

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.

[parry2k]

can you post sample data with expected result, it will help 🙂

[Anonymous]

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 !

[Ashish_Mathur]

Hi,

Please explain the question is simple non technical language.  What exactly are you trying to compute?

[Anonymous]

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 🙂

[v-yulgu-msft]

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

[parry2k]

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.

[Anonymous]

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.

[OwenAuger · Accepted 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] ) )
)

[Anonymous]

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! 

[parry2k]

can you provide what you are expected to be output of sample data you shared in pbix

[parry2k]

is this the output you are expecting from your sample data