Forum Discussion
Continued ContributorDistinct Count of Column Combinations?
Please help!
I want a measure that counts the number of distinct combinations of 3 columns, in a table.
Say my table is FactSales, with these nine columns: SalesId, DimKey1, DimKey2, DimKey3, DimKey4, DimKey5, Fact1, Fact2, Fact3
I want a measure that returns the number of distinct combinations of DimKey3, DimKey4 and DimKey5.
My first attempt, and what I was hoping would work, was this...
DimKey3/4/5 Combinations =
COUNTROWS(
SUMMARIZECOLUMNS(
'Fact - Sales'[DimKey3]
,'Fact - Sales'[DimKey4]
,'Fact - Sales'[DimKey5]
)
)...however, it seems that SUMMARIZECOLUMNS cannot be used in this context.
How can I do it with a measure?
Still need an answer on sample data but here is something that might work for you or get you started down the right path. This measure here:
DistinctCombos = VAR tmptable1 = SELECTCOLUMNS(Sales,"DimKey3",[DimKey3],"DimKey4",[DimKey4],"DimKey5",[DimKey5]) VAR tmptable2 = DISTINCT(tmptable1) VAR distcount = COUNTROWS(tmptable2) RETURN distcount
I used with this table
Sales
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrKKU0FUkWpKUAyvSg1NU8pVidayQghhaQCJGOMrgnMAcmYwE1A0DBdpljk4BrN4GYhSJC4OYYj4FossJiHcL0lQge6QwwNcHg6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesID = _t, DimKey3 = _t, DimKey4 = _t, DimKey5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"DimKey3", type text}, {"DimKey4", type text}, {"DimKey5", type text}}) in #"Changed Type"The measure returns 9 for this, which is correct because order matters. In other words if I have red, green, blue, that is not the same as having green, red, blue. Those are two different combinations in this case. If you don't want that, give me the data to play with and we'll have a look.
5 Replies
kevhavI've thought that I could add a column that is the concatenation of DimKey3 & DimKey4 & DimKey5; and then do a DISTINCTCOUNT on this column.
Or, I could use SUMMARIZECOLUMNS to add a "summary table" to my data model, and then do COUNTROWS on that summary table.
But I would like to do it strictly with a measure, if possible.
I found this related post, in which one Microsoft rep said, back in 2016, "Currently I don’t think only using a measure could achieve this." But I feel like it should be possible without adding anything else to my data model!
- Greg_Deckler
Community Champion
This sounds like an interesting problem and I would like to help. Please post sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
- Greg_Deckler
Still need an answer on sample data but here is something that might work for you or get you started down the right path. This measure here:
DistinctCombos = VAR tmptable1 = SELECTCOLUMNS(Sales,"DimKey3",[DimKey3],"DimKey4",[DimKey4],"DimKey5",[DimKey5]) VAR tmptable2 = DISTINCT(tmptable1) VAR distcount = COUNTROWS(tmptable2) RETURN distcount
I used with this table
Sales
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrKKU0FUkWpKUAyvSg1NU8pVidayQghhaQCJGOMrgnMAcmYwE1A0DBdpljk4BrN4GYhSJC4OYYj4FossJiHcL0lQge6QwwNcHg6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesID = _t, DimKey3 = _t, DimKey4 = _t, DimKey5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"DimKey3", type text}, {"DimKey4", type text}, {"DimKey5", type text}}) in #"Changed Type"The measure returns 9 for this, which is correct because order matters. In other words if I have red, green, blue, that is not the same as having green, red, blue. Those are two different combinations in this case. If you don't want that, give me the data to play with and we'll have a look.