Forum Discussion

JFG1234's avatar
JFG1234
Frequent Visitor
3 years ago
Solved

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

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 ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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