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

Anonymous 

 

Thank you for replying.

The answer is almost what I was looking for. Only the final result is not what I am trying to achieve.

 

I am looking for a result like this:

    Month Name        September Teams Active        October Teams Active    
    August        80%        40%    
    September        100%        60%    
    October        60%        100%    

 

So based on the teams that were active in a certain month I want the percentage of matches with other months e.g. 10 teams are active in September, this should give 100% on the coordinate (September Teams Active, September) and if we find 4 matches compared to October it should display 40% and 7 matches in August should display 70%.

 

As suggested this is a sample of the data of the "monthly team/club activity" dataset that I masked accordingly. There are no duplicate team_id's per month and monthly active is irrelevant for my question.

    team_id        Month Name        Month        Year        Monthly Active    
    1        October        10        2022        10    
    2        October        10        2022        13    
    3        October        10        2022        8    
    4        October        10        2022        6    
    1        September        9        2022        3    
    2        September        9        2022        5    
    3        September        9        2022        1    
    7        September        9        2022        4    
    10        September        9        2022        9    

 

For this sample the result would be the following:

    Month Name        September Teams Active        October Teams Active    
    September        100%        75%    
    October        60%        100%    
Anonymous's avatar
Anonymous
Not applicable
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 )