Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX: Calculate intersect based on subset and condition

Hi community,   I have been stuck with the following.   I want to grab a vector subset that is conditioned on a specific month and compare that to the main dataset (also in vector format for comp...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    You can create a measure as below to get it, please find the details in the attachment.

    Active percentage = 
VAR _selmonth =
    SELECTEDVALUE ( 'Monthly Team/Club Activity'[Month] )
VAR _ctab =
    CALCULATETABLE (
        VALUES ( 'Monthly Team/Club Activity'[team_id] ),
        FILTER (
            ALLSELECTED ( 'Monthly Team/Club Activity' ),
            'Monthly Team/Club Activity'[Month] = _selmonth
        )
    )
VAR _ptab =
    CALCULATETABLE (
        VALUES ( 'Monthly Team/Club Activity'[team_id] ),
        FILTER (
            ALLSELECTED ( 'Monthly Team/Club Activity' ),
            'Monthly Team/Club Activity'[Month] = _selmonth - 1
        )
    )
VAR _steams =
    INTERSECT ( _ptab, _ctab )
VAR _scount =
    COUNTROWS ( _steams )
VAR _ccount =
    CALCULATE ( DISTINCTCOUNT ( 'Monthly Team/Club Activity'[team_id] ) )
RETURN
    DIVIDE ( _scount, _ccount )

     

    If the above ones can't help you get the desired result, please provide some sample data in your table 

    'Monthly Team/Club Activity' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

  • Anonymous's avatar
    Anonymous
    3 years ago

    I got the answer I wanted with a small adaptation of the other accepted solution.

     

    September Active 2 = 

    VAR selectedMonth =

        SELECTEDVALUE ( 'Monthly Team/Club Activity'[Month] )

    VAR calculatedTable =

        CALCULATETABLE (

            VALUES ( 'Monthly Team/Club Activity'[team_id] ),

            FILTER (

                ALLSELECTED ( 'Monthly Team/Club Activity' ),

                'Monthly Team/Club Activity'[Month] = selectedMonth

            )

        )

    VAR calculatedTable2 =

        CALCULATETABLE (

            VALUES ( 'Monthly Team/Club Activity'[team_id] ),

            FILTER (

                ALLSELECTED ( 'Monthly Team/Club Activity' ),

                'Monthly Team/Club Activity'[Month] = 9

            )

        )

    VAR teamsIntersect =

        INTERSECT ( calculatedTable2, calculatedTable )

    VAR amountOfTeamsIntersect =

        COUNTROWS ( teamsIntersect )

    VAR amountOfTeamsInMonth =

        CALCULATE ( DISTINCTCOUNT ( 'Monthly Team/Club Activity'[team_id] ) )

    RETURN

        DIVIDE ( amountOfTeamsIntersect, amountOfTeamsInMonth )
Anonymous's avatar
Anonymous
Not applicable
3 years ago

lbendlin 

 

Thank you for the reply and advice.

In my other answer I provided the sanitized sample data and a more in depth example of what I am trying to achieve.