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

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