Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
PARTY_ID | Distinct Count of Flags |
11111 | 21 |
222222 | 17 |
333333 | 16 |
444444 | 16 |
555555 | 16 |
666666 | 15 |
777777 | 15 |
888888 | 15 |
999999 | 15 |
10101010 | 15 |
121212121 | 14 |
131313131 | 14 |
141414141 | 14 |
151515151 | 14 |
161616161 | 14 |
171717171 | 14 |
181818181 | 14 |
191919191 | 14 |
202020202 | 14 |
212212121 | 14 |
I need to have some dax so that I can put the above into the following format for a clustered bar chart:
countofPARTY_ID | Distinct Count of Flags |
1 | 21 |
1 | 17 |
3 | 16 |
5 | 15 |
10 | 14 |
i have succesfully got the data out in this format via SQL, but I do not want to lose the PARTY_ID so that I can keep the drill-through capability.
Thanks!
Solved! Go to Solution.
Hi,
In the first example,
Please try "don't summarize" [Distinct count of flags] column in the visualization.
Or,
if you do not have [Distincgt count of flags] column for the axis, like the second example,
please try something like the attached pbix file below.
Count of party measure: =
VAR _distinctcountnumber =
SELECTEDVALUE ( 'Axis calculated table'[Distinct Count of Flags] )
VAR _t =
ADDCOLUMNS (
VALUES ( Data[PARTY_ID] ),
"@distinctcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[FLAG_ID] ) ) )
)
VAR _resulttable =
FILTER ( _t, [@distinctcount] = _distinctcountnumber )
RETURN
COUNTROWS ( _resulttable )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim , your screenshot shows the needed result but the solution doesn't work. Here is what happens when I do the solution:
It just gives me the number of rows in the table and the count of distinct flags in that table.
Here is an example of the raw data:
PARTY_ID | FLAG_ID |
11111 | a |
11111 | b |
11111 | c |
22222 | a |
22222 | b |
22222 | c |
33333 | a |
44444 | a |
44444 | b |
44444 | c |
44444 | d |
44444 | e |
44444 | f |
44444 | g |
44444 | h |
55555 | a |
55555 | b |
66666 | a |
66666 | b |
77777 | a |
77777 | b |
88888 | a |
88888 | b |
99999 | a |
99999 | b |
And here is the expected result for the above:
countofPARTY_ID | Distinct Count of Flags |
2 | 3 |
1 | 1 |
1 | 8 |
5 | 2 |
hope this makes sense!
Hi,
In the first example,
Please try "don't summarize" [Distinct count of flags] column in the visualization.
Or,
if you do not have [Distincgt count of flags] column for the axis, like the second example,
please try something like the attached pbix file below.
Count of party measure: =
VAR _distinctcountnumber =
SELECTEDVALUE ( 'Axis calculated table'[Distinct Count of Flags] )
VAR _t =
ADDCOLUMNS (
VALUES ( Data[PARTY_ID] ),
"@distinctcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[FLAG_ID] ) ) )
)
VAR _resulttable =
FILTER ( _t, [@distinctcount] = _distinctcountnumber )
RETURN
COUNTROWS ( _resulttable )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
114 | |
44 | |
44 | |
28 | |
22 |