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

[JFG1234]

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

[v-yiruan-msft · Accepted 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

[JFG1234]

Awesome work, unreal!