Forum Discussion
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 comparison) and count the amount of rows their intersect gives.
This is my dataset:
Per row this dataset has a distinct team_id per month.
I want to grab a vector of the team_id's in a specific month say September and compare them dynamically in a table so that I can see the percentage of teams that were active in September and also showed activity in October. This would look like:
| September Active | |
| August | 0% |
| September | 100% |
| October | 70% |
Now comes the part where I am stuck. My DAX code looks like this:
I make a variable vector containing only the team_id's from the month September then I make a variable vector from the main dataset (to my knowledge) containing the team_id's of the current month in the dynamic table. In the end I use intersect to obtain the percentage of matching team_id's.
Still my current result looks like:
Somewhere something goes wrong, I hope anyone could point out my mistake or the mistake in my approach.
Thank you for your time all!
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 )
5 Replies
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
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 )
- lbendlin
Super User
Your approach sounds reasonable. Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank 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.
