Declare scoped variable in table calculation

[Molotch]

var currentWeek = LOOKUPVALUE(Date[Week];Date[Date];TODAY())

var t = SELECTCOLUMNS(User
                ;"UserId";User[Id]
                ;"User";User[User]
                ;"Upcoming vacation"; 
                CALCULATE(MIN(ActivityList[YearWeek]);ActivityList[ActivityType] = "Vacation"; ActivityList[YearWeek] >= currentWeek)
                ;"BU";RELATED(Organisation[Organisation L3])
            )

I would like to use the result of the CALCULATE statement as a filter value in another column. That way I can use the first upcoming vacation week for each user to get that row from ActivityList and get the other values related to the first vacation week.

Since I can't use MIN in a filter statement I can't just copy paste the CALCULTATE statement and use that as a filter to get the correct week.

I've tried figuring out another way to write this query by using joins but DAX doesn't seem to like joins on calculated tables.

Anyone got an idea on how to proceed?

[v-yulgu-msft]

Hi @Molotch,

Based on current description, I am not very clear about what you were trying to achieve. Would you please post sample data and illustrate desired result with examples?

"I've tried figuring out another way to write this query by using joins but DAX doesn't seem to like joins on calculated tables."

You can CROSSJOIN two calculated tables then add filters to it based on specific scenario.

Regards,

Yuliana Gu

[Molotch]

I have two tables, one with Users, one with Activities.

I would like to combine them and show a resulting table showing the first occurance of a particular activity with chosen activity properties.

So from the dataset below I would like to show two rows, one for each user containing the first Vacation occurance:

Resulting table
John Doe, Vacation, 10, "Test2"

Jane Doe, Vaction, 11, "Test7"

Id, Name
0, John Doe
1, Jane Doe

UserId, ActivityType, Week,   Info1

0, Work, 09, "Test1"

0, Vacation, 10, "Test2"
0, Vacation, 11, "Test3"

0, Vacation, 12, "Test4"

0, Work, 13, "Test5"

1, Work, 10, "Test6"
1, Vacation, 11, "Test7"

1, Vacation, 12, "Test8"

1, Vacation, 13, "Test9"

1, Work, 14, "Test10"

My first plan (above) was to try and declare a variable of the week of the first occurance and then use that for a lookup of the other properties of that week. But maybe trying to group the table by User and ActicityType and sort each grouping by Week is a better solution if there's a way to get the first row from each sorted grouping.

[Molotch]

Noone? I've tried all sort of table calculations but I can't seem to solve the problem. There ought to be a way to do this in DAX.