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.
I have a source table that looks like this:
ID | Round | Result |
1 | 1 | 6 |
2 | 1 | 4 |
3 | 1 | 3 |
4 | 1 | 5 |
1 | 2 | 6 |
2 | 2 | 6 |
3 | 2 | 2 |
4 | 2 | 1 |
1 | 3 | 3 |
3 | 3 | 8 |
5 | 3 | 5 |
I want to create a visualisation that enables the end user to visualise the changes between each Rounds Result dynamically.
For example in scenario 1, I want to compare the first Round (1) vs the second round (2) using interactive filters:
I want to end up with the visualisation on the right which is table of counts based on whether there is a positive, negative, no change, ID removed or added (will get to this). The Data Step shows what intuitively my brain suggests to do.
The second scenario is what occurs when there is drop out or IDs added, here we compare Round 1 vs Round 3;
Here we can see that there is no result in round 3 for ID 2 and 4 there is no follow-up result but there is a new ID added (5).
I have seen something like this generated in Tableau but have no idea how it would translate to Power Bi. Any help would be appreciated.
Cheers
Solved! Go to Solution.
Hi @JFG1234 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create two round dimension tables
2. Create two separated slicers and apply the round field of the above dimension tables
3. Create the measures as below to get the result for the different round selectors
Result Round A =
VAR _selr1 =
SELECTEDVALUE ( 'Round A'[Round] )
RETURN
CALCULATE (
SUM ( 'Table'[Result] ),
FILTER ( 'Table', 'Table'[Round] = _selr1 )
)
Result Round B =
VAR _selr1 =
SELECTEDVALUE ( 'Round B'[Round] )
RETURN
CALCULATE (
SUM ( 'Table'[Result] ),
FILTER ( 'Table', 'Table'[Round] = _selr1 )
)
4. Create a dimension table as below using "Enter data" method
5. Create the measures as below to get the count of IDs for different status
Measure =
VAR _selstatus =
SELECTEDVALUE ( 'Status'[Status] )
VAR _selr1 =
SELECTEDVALUE ( 'Round A'[Round] )
VAR _selr2 =
SELECTEDVALUE ( 'Round B'[Round] )
VAR _tab =
SUMMARIZE (
FILTER ( 'Table', 'Table'[Round] IN { _selr1, _selr2 } ),
'Table'[ID],
"@r1", [Result Round A],
"@r2", [Result Round B]
)
RETURN
SWITCH (
_selstatus,
"Added", COUNTX ( FILTER ( _tab, ISBLANK ( [@r1] ) && NOT ( ISBLANK ( [@r2] ) ) ), [ID] ),
"Removed", COUNTX ( FILTER ( _tab, ISBLANK ( [@r2] ) && NOT ( ISBLANK ( [@r1] ) ) ), [ID] ),
"Negative",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] < 0
),
[ID]
),
"No Change",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] = 0
),
[ID]
),
"Positive",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] > 0
),
[ID]
)
) + 0
Measure 2 = SUMX ( VALUES ( 'Status'[Status] ), [Measure] )
6. Create a matrix visual as below screenshot
Best Regards
Hi @JFG1234 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create two round dimension tables
2. Create two separated slicers and apply the round field of the above dimension tables
3. Create the measures as below to get the result for the different round selectors
Result Round A =
VAR _selr1 =
SELECTEDVALUE ( 'Round A'[Round] )
RETURN
CALCULATE (
SUM ( 'Table'[Result] ),
FILTER ( 'Table', 'Table'[Round] = _selr1 )
)
Result Round B =
VAR _selr1 =
SELECTEDVALUE ( 'Round B'[Round] )
RETURN
CALCULATE (
SUM ( 'Table'[Result] ),
FILTER ( 'Table', 'Table'[Round] = _selr1 )
)
4. Create a dimension table as below using "Enter data" method
5. Create the measures as below to get the count of IDs for different status
Measure =
VAR _selstatus =
SELECTEDVALUE ( 'Status'[Status] )
VAR _selr1 =
SELECTEDVALUE ( 'Round A'[Round] )
VAR _selr2 =
SELECTEDVALUE ( 'Round B'[Round] )
VAR _tab =
SUMMARIZE (
FILTER ( 'Table', 'Table'[Round] IN { _selr1, _selr2 } ),
'Table'[ID],
"@r1", [Result Round A],
"@r2", [Result Round B]
)
RETURN
SWITCH (
_selstatus,
"Added", COUNTX ( FILTER ( _tab, ISBLANK ( [@r1] ) && NOT ( ISBLANK ( [@r2] ) ) ), [ID] ),
"Removed", COUNTX ( FILTER ( _tab, ISBLANK ( [@r2] ) && NOT ( ISBLANK ( [@r1] ) ) ), [ID] ),
"Negative",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] < 0
),
[ID]
),
"No Change",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] = 0
),
[ID]
),
"Positive",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] > 0
),
[ID]
)
) + 0
Measure 2 = SUMX ( VALUES ( 'Status'[Status] ), [Measure] )
6. Create a matrix visual as below screenshot
Best Regards
Awesome work, unreal!
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |