Forum Discussion
DAX: Calculate intersect based on subset and condition
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
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 )
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
How to upload PBI in Community
Best Regards
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% |
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 )