cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JFG1234
Frequent Visitor

Use multiple selectors to dynamically join tables to create calculation with DAX and Power BI

I have a source table that looks like this:

 

IDRoundResult
116
214
313
415
126
226
322
421
133
338
535

 

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.

JFG1234_3-1675129703098.png

 

 

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).

 

JFG1234_2-1675129693781.png

 

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

 

 

 

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 

yingyinr_1-1675239521098.png

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

yingyinr_2-1675239708961.png

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

yingyinr_0-1675239420766.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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 

yingyinr_1-1675239521098.png

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

yingyinr_2-1675239708961.png

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

yingyinr_0-1675239420766.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Awesome work, unreal!

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

Top Kudoed Authors