Trouble with syntax of right column in INTERSECT function.

[UBComma]

I am running an analysis of people who live in a community of buildings called A, B and C and I want to know where these people chose to live in following years. They may: Live in the same building, move to another building, or decide to live outside the community.

Data

ID	Year	Residence	Annual Resident Count by Building	Count of Residents who stayed in the same building from the prior year
123	2019	A	1
246	2019	B	2
356	2019	C	1
467	2019	B	2
123	2020	A	2	1
246	2020	C	1	0
356	2020	Else	1
467	2020	A	2	1
578	2020	B	1	0

To get the [Annual Resident Count by Building] I am using:
SUMMARIZECOLUMNS (

    Data[Residence],

    Data[Year],

    Data[ID],

    "Annual Resident Count by Building",

        CALCULATE (

            COUNT ( Data[ID] ),

            REMOVEFILTERS ( Data[ID] )))

Now I want to add another column which is the [Count of Residents who stayed in the same building from the prior year]

I am trying to do this using INTERSECT between a column of ID’s from the year in the row context and a column of ID’s in a modified row context where they are the ID’s of the Residents from the next year. Intersect will give me the common ID’s, I can COUNTROWS and know how many residents in the following year chose to live in the same building.

My problem is getting the syntax of the INTERSECT function to work with the right column. I have tried things like the following syntax for the right column but I’m not getting anything. If anyone can help it will be greatly appreciated. I have simplified this from my actual model and may have messed up the DAX a little in the process but hopefully this is enough so you can see what I’m trying to accomplish. Forgive me if I’m doing something dumb, I’m still fairly new to DAX.

VAR Building = SELECTEDVALUE ( Data[Building] )
VAR CohortYear = SELECTEDVALUE ( Data[Year] )

RETURN

// Omitting DAX for preceding columns, this is where I run into problems

       " Count of Residents who stayed in the same building from the prior year ",
            COUNTROWS (
                INTERSECT (
                    SELECTCOLUMNS (
                        'Data',
                        "@Resident", Data[ID]
                    ),
                    SELECTCOLUMNS (
                        FILTER (
                            ALLSELECTED ( Data),
                            Data[Building] = Building
                                && Data[Year] = CohortYear + 1
                        )))

Can anyone help me get the syntax for the right column?

[AlexisOlson]

For INTERSECT to work, both tables need to have the same number of columns. In your DAX, the left table has only one column but the right table has as many columns as Data has.

I'd recommend using variables to make this a bit easier to read:

VAR Building = SELECTEDVALUE ( Data[Building] )
VAR CohortYear = SELECTEDVALUE ( Data[Year] )
VAR CohortIDs =
    CALCULATETABLE (
        VALUES ( Data[ID] ),
        FILTER (
            ALLSELECTED ( Data ),
            Data[Building] = Building && Data[Year] = CohortYear
        )
    )
VAR PrevIDs =
    CALCULATETABLE (
        VALUES ( Data[ID] ),
        FILTER (
            ALLSELECTED ( Data ),
            Data[Building] = Building && Data[Year] = CohortYear - 1
        )
    )
RETURN
    COUNTROWS ( INTERSECT ( CohortIDs, PrevIDs ) )

[bcdobbs]

Nice clean code!

[UBComma]

Thank you @AlexisOlson .  That's helpful and I thought I should be using the VALUES function but wasn't sure how to bring that together with CALUCULATETABLE as you did. I'm working on this now, still having difficulty but hopefully I can get it to work. Below is the DAX from my actual model. I think the syntax is right but I'm getting nothing returned in DAX Studio. I tried just returning one variable, CohortIDs and manualing inputing a known valid value for Building and CohortYear, and I get results. For some reason SELECTEDVALUE doesn't seem to be working. I'll keep working on it and appreciate your guidance.

EVALUATE
VAR Building =
    SELECTEDVALUE ( Data[Building Aggregate] )
VAR CohortYear =
    SELECTEDVALUE ( Data[Fall Year] )
VAR CohortIDs =
    CALCULATETABLE (
        VALUES ( Data[Student ID] ),
        FILTER (
            ALLSELECTED ( Data ),
            Data[Building Aggregate] = Building
                && Data[Fall Year] = CohortYear
        )
    )
VAR CohortIDs1Y =
    CALCULATETABLE (
        VALUES ( Data[Student ID] ),
        FILTER (
            ALLSELECTED ( Data ),
            Data[Building Aggregate] = Building
                && Data[Fall Year] = CohortYear + 1
        )
    )
RETURN
    SUMMARIZECOLUMNS (
        Data[Building Aggregate],
        Data[Fall Year],
        "Cohort Count", COUNTROWS ( CohortIDs ),
        "Cohort Remaning Next Year", COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs1Y ) )
    )

[AlexisOlson · Accepted solution]

There's a couple of problems here. First, SELECTEDVALUE returns blank if there are multiple values instead of just a single one. Second, variables are constant once they're defined, so you'll get the same "Cohort..." values for every row.

If you're doing this as a query, then calculating the IDs needs to happen inside the SUMMARIZECOLUMNS. See if this works:

EVALUATE
SUMMARIZECOLUMNS (
    Data[Building Aggregate],
    Data[Fall Year],
    "Cohort Count", CALCULATE ( COUNT ( Data[Student ID] ) ),
    "Cohort Remaning Next Year",
        VAR CohortIDs = CALCULATETABLE ( VALUES ( Data[Student ID] ) )
        VAR CohortYear = Data[Fall Year]
        VAR CohortIDs1Y =
            CALCULATETABLE (
                VALUES ( Data[Student ID] ),
                Data[Fall Year] = CohortYear + 1
            )
        RETURN
            COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs1Y ) )
)

[UBComma]

@AlexisOlson That is genius 😁 thank you so much. I have been working on this problem for days and really stuck. My wife thanks you too 😉. With your comments about SELECTEDVALUED and VAR I understand much better how they function. Below is the final DAX which includes a second year and percentages. The data is revealing some interesting trends fortunately, if it didn't I would have been a little disappointed for having done all this work - but in the end I learned a lot about DAX.

One last question if I may. When calculating the percentages I'm repeating a lot of DAX from the prior calculations. It seems this shouldn't be neessary but I did not know how to access the calculations in the prior columns. Is there a way to do that? It would simplifiy the code a lot. This is what I have now, it works. If you would like a sample PBI file to see results, I will be happy to send that to you.

Building Cohorts =

SUMMARIZECOLUMNS (
    Data[Building Aggregate],
    Data[Fall Year],
    "Cohort Count", COUNT ( Data[Student ID] ),
    "Cohort Remaning Next Year",
        VAR CohortIDs =
            CALCULATETABLE ( VALUES ( Data[Student ID] ) )
        VAR CohortYear =
            MIN ( Data[Fall Year] )
        VAR CohortIDs1Y =
            CALCULATETABLE ( VALUES ( Data[Student ID] ), Data[Fall Year] = CohortYear + 1 )
        RETURN
            COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs1Y ) ),
    "Perc Returned 1",
        DIVIDE (
            VAR CohortIDs =
                CALCULATETABLE ( VALUES ( Data[Student ID] ) )
            VAR CohortYear =
                MIN ( Data[Fall Year] )
            VAR CohortIDs1Y =
                CALCULATETABLE ( VALUES ( Data[Student ID] ), Data[Fall Year] = CohortYear + 1 )
            RETURN
                COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs1Y ) ),
            COUNT ( Data[Student ID] )
        ),
    "Cohort Remaning Year 2",
        VAR CohortIDs =
            CALCULATETABLE ( VALUES ( Data[Student ID] ) )
        VAR CohortYear =
            MIN ( Data[Fall Year] )
        VAR CohortIDs2Y =
            CALCULATETABLE ( VALUES ( Data[Student ID] ), Data[Fall Year] = CohortYear + 2 )
        RETURN
            COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs2Y ) ),
    "Perc Returned 2",
        DIVIDE (
            VAR CohortIDs =
                CALCULATETABLE ( VALUES ( Data[Student ID] ) )
            VAR CohortYear =
                MIN ( Data[Fall Year] )
            VAR CohortIDs2Y =
                CALCULATETABLE ( VALUES ( Data[Student ID] ), Data[Fall Year] = CohortYear + 2 )
            RETURN
                COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs2Y ) ),
            COUNT ( Data[Student ID] )
        )
)

[AlexisOlson]

Getting the percentages can be done more efficiently using ADDCOLUMNS.

Building Cohorts =
ADDCOLUMNS (
    SUMMARIZECOLUMNS (
        Data[Building Aggregate],
        Data[Fall Year],
        "Cohort Count", COUNT ( Data[Student ID] ),
        "Cohort Remaning Next Year",
            VAR CohortIDs = CALCULATETABLE ( VALUES ( Data[Student ID] ) )
            VAR CohortYear = MIN ( Data[Fall Year] )
            VAR CohortIDs1Y =
                CALCULATETABLE (
                    VALUES ( Data[Student ID] ),
                    Data[Fall Year] = CohortYear + 1
                )
            RETURN
                COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs1Y ) ),
        "Cohort Remaning Year 2",
            VAR CohortIDs = CALCULATETABLE ( VALUES ( Data[Student ID] ) )
            VAR CohortYear = MIN ( Data[Fall Year] )
            VAR CohortIDs2Y =
                CALCULATETABLE (
                    VALUES ( Data[Student ID] ),
                    Data[Fall Year] = CohortYear + 2
                )
            RETURN
                COUNTROWS ( INTERSECT ( CohortIDs, CohortIDs2Y ) )
    ),
    "Perc Returned 1", DIVIDE ( [Cohort Remaning Next Year], [Cohort Count] ),
    "Perc Returned 2", DIVIDE ( [Cohort Remaning Year 2], [Cohort Count] )
)

 It's not quite so easy to reuse the CohortIDs variables since those are tables rather than single values. It's possible but not likely worth the effort.