DAX: Calculate intersect based on subset and condition

[Anonymous]

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!

[v-yiruan-msft · Accepted solution]

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

[v-yiruan-msft · Accepted solution]

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]

@v-yiruan-msft 

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 · Accepted solution]

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]

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-...
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/1447523

[Anonymous]

@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.