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.
Hi,
I have 1 table with the first column as ID:
1 X Y
1 X1 Y
2 X Z
2 X1 Z
3 X2 Z
1 X4 F
After this table is filterd by a slicer on the report, Let's say Y and Z from 3rd column are chosen, I want to create a count of all the possible combinations from the second column. In this case:
Combination Count
X,X1 2 (there are 2 ID's with X,X1)
X2 1 (there is 1 ID with X2)
Is that possible please?
Solved! Go to Solution.
Hi @Asking
This not as simple as it sounds. The reason is that there is no column to slice by. Long story short, please refer to attched file and following screenshots. You have to have either index or Date column. If don't, then use power query to add an index column. Please let me know if you need any further help.
Combination =
VAR T1 =
SUMMARIZE (
'Table',
'Table'[Column1],
"@Index", MAX ( 'Table'[Index] ),
"@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
)
VAR T2 =
SUMMARIZE (
T1,
[@Combination],
"@@Index", MAX ( 'Table'[Index] )
)
VAR T3 =
ADDCOLUMNS (
T2,
"@Rank", RANKX ( T2, [@@Index],, ASC, Dense )
)
RETURN
MAXX (
FILTER ( T3, [@Rank] = SELECTEDVALUE ( 'Table 2'[Value] ) ),
[@Combination]
)
Count =
VAR T1 =
SUMMARIZE (
'Table',
'Table'[Column1],
"@Index", MAX ( 'Table'[Index] ),
"@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
)
VAR T2 =
SUMMARIZE (
T1,
[@Combination],
"@@Index", MAX ( 'Table'[Index] ),
"@Count", COUNTROWS ( FILTER ( T1, [@Combination] = EARLIER ( [@Combination] ) ) )
)
VAR T3 =
ADDCOLUMNS (
T2,
"@Rank", RANKX ( T2, [@@Index],, ASC, Dense )
)
RETURN
MAXX (
FILTER ( T3, [@Rank] = SELECTEDVALUE ( 'Table 2'[Value] ) ),
[@Count]
)
Hi @Asking
This not as simple as it sounds. The reason is that there is no column to slice by. Long story short, please refer to attched file and following screenshots. You have to have either index or Date column. If don't, then use power query to add an index column. Please let me know if you need any further help.
Combination =
VAR T1 =
SUMMARIZE (
'Table',
'Table'[Column1],
"@Index", MAX ( 'Table'[Index] ),
"@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
)
VAR T2 =
SUMMARIZE (
T1,
[@Combination],
"@@Index", MAX ( 'Table'[Index] )
)
VAR T3 =
ADDCOLUMNS (
T2,
"@Rank", RANKX ( T2, [@@Index],, ASC, Dense )
)
RETURN
MAXX (
FILTER ( T3, [@Rank] = SELECTEDVALUE ( 'Table 2'[Value] ) ),
[@Combination]
)
Count =
VAR T1 =
SUMMARIZE (
'Table',
'Table'[Column1],
"@Index", MAX ( 'Table'[Index] ),
"@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
)
VAR T2 =
SUMMARIZE (
T1,
[@Combination],
"@@Index", MAX ( 'Table'[Index] ),
"@Count", COUNTROWS ( FILTER ( T1, [@Combination] = EARLIER ( [@Combination] ) ) )
)
VAR T3 =
ADDCOLUMNS (
T2,
"@Rank", RANKX ( T2, [@@Index],, ASC, Dense )
)
RETURN
MAXX (
FILTER ( T3, [@Rank] = SELECTEDVALUE ( 'Table 2'[Value] ) ),
[@Count]
)
Thank you. That helped.
Is there a way I can show the result as a stacked bar chart. I would like the "combination" to be in the Y axis and the "count" in the X but I understand I can't put a measure in the Y...
Hi @Asking
I hope this is what you're looking for. Please refer to attached updated sample file.
Combinations =
SELECTCOLUMNS (
SUMMARIZE (
'Table',
'Table'[Column1],
"@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
),
"Combination",
[@Combination]
)
Count2 =
VAR T1 =
SUMMARIZE (
'Table',
'Table'[Column1],
"@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
)
VAR T2 =
SUMMARIZE (
T1,
[@Combination],
"@Count", COUNTROWS ( FILTER ( T1, [@Combination] = EARLIER ( [@Combination] ) ) )
)
RETURN
MAXX (
FILTER ( T2, [@Combination] = SELECTEDVALUE ( Combinations[Combination] ) ),
[@Count]
)
Thank you.
However, how do I create the actual table and not only get the scalar value please?
@Asking Try this:
Measure =
VAR __Table = ADDCOLUMNS(SUMMARIZE('Table',[Column3]),"__Text",CONCATENATEX('Table',[Column2],","))
VAR __Values = DISTINCT(SELECTCOLUMNS(__Table,"__Text",[__Text]))
VAR __Table1 = ADDCOLUMNS(SUMMARIZE('Table',[Column1]),"__Text",CONCATENATEX('Table',[Column2],","))
VAR __Table2 = FILTER(__Table1,[__Text] IN __Values)
VAR __Result = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table2,"__Index",[Column1])))
RETURN
__Result
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |