How to use successive table VARiables?

[Anonymous]

I am trying to build up a complex DAX measure by doing separate steps into table variables. The measure uses VARs tmp1 thru tmp5, all building on the previous tmp VAR tables. I am now trying to create VAR tmp6:

VAR tmp6 = SUMMARIZE(tmp5, [AccountNo], [StartDate], "FirstAnotherDate", MIN([AnotherDate]))

but Power BI gives me this error: Column 'AnotherDate' cannot be found or used in this expression.

I don't understand tbecause both [StartDate] and [AnotherDate] fields were referenced in the VAR tmp5 definition with no error.
Any ideas?

[Greg_Deckler]

@Anonymous - Would need to see the whole formula. But perhaps you should be using ADDCOLUMNS here.

[Anonymous]

Here is the whole DAX formula. Each step up to tmp5 works. Only when I add step tmp6 do I get the error.

Edit: I've added comments to each step to explain what I'm trying to do.

-- Get all the rows from fact table 1 where the flag matches. Only get data for test account 12345.
VAR tmp1 = FILTER(ALL('FactTable1'), RELATED('DimensionTable1'[Dim1Attr1]) = "Data of interest" && RELATED('Account'[FullAccountNumber]) = 12345)

-- For each account, get the earliest of StartDate.
-- We need to create a new column for "AccountNo" with no lineage by adding 0. This allows us to join on this column at step tmp4.
VAR tmp2 = SELECTCOLUMNS(
    SUMMARIZE(tmp1, [AccountNo], "StartDate", MIN([StartDate])),
    "AccountNo", [AccountNo] + 0,
    "StartDate", [StartDate]
)

-- Get all the rows from fact table 2 where the flag matches.
-- We need to create a new column for "AccountNo" with no lineage by adding 0. This allows us to join on this column at step tmp4.
VAR tmp3 = SELECTCOLUMNS(
    FILTER(ALL('FactTable2'), RELATED('DimensionTable3'[Dim3Attr1]) = "Other data of interest"),
    "AccountNo", [AccountNo] + 0,
    "AnotherDate", [AnotherDate]
)

-- Join the fact tables together across [AccountNo].
VAR tmp4 = NATURALLEFTOUTERJOIN(tmp2, tmp3)

-- Only interested in data where [AnotherDate] >= [StartDate]
VAR tmp5 = FILTER(tmp4, [AnotherDate] >= [StartDate])

-- For each [AccountNo] and [StartDate], get the earliest date of [AnotherDate]
VAR tmp6 = SUMMARIZE(tmp5, [AccountNo], [StartDate], "FirstAnotherDate", MIN([AnotherDate]))

[Greg_Deckler · Accepted solution]

@Anonymous - So what happens if you just return tmp5 in a Table formula, what does it look like? Also, you might try GROUPBY instead of SUMMARIZE

VAR tmp6 = GROUPBY(tmp5, [AccountNo], [StartDate], "FirstAnotherDate", MINX(CURRENTGROUP(),[AnotherDate]))

[Anonymous]

Using the GROUPBY with CURRENTGROUP() worked. Thank you, @Greg_Deckler 

[Anonymous]

I have another problem with this measure. VAR tmp3 must only look at data for the [AccountNo] that exist in tmp1, as it is very slow if it loads for every AccountNo when only a few have been selected in the filter. I have tried this in the filter for tmp3 as per below, but it gives an error: "The syntax for 'in' is incorrect"

How can this be achieved?

-- Get all the rows from fact table 1 where the flag matches. Only get data for test account 12345.
VAR tmp1 = FILTER(ALL('FactTable1'), RELATED('DimensionTable1'[Dim1Attr1]) = "Data of interest" && RELATED('Account'[FullAccountNumber]) = 12345)

-- For each account, get the earliest of StartDate.
-- We need to create a new column for "AccountNo" with no lineage by adding 0. This allows us to join on this column at step tmp4.
VAR tmp2 = SELECTCOLUMNS(
    SUMMARIZE(tmp1, [AccountNo], "StartDate", MIN([StartDate])),
    "AccountNo", [AccountNo] + 0,
    "StartDate", [StartDate]
)

-- Get all the rows from fact table 2 where the flag matches.
-- We need to create a new column for "AccountNo" with no lineage by adding 0. This allows us to join on this column at step tmp4.
VAR tmp3 = SELECTCOLUMNS(
    FILTER(ALL('FactTable2'),
	RELATED('DimensionTable3'[Dim3Attr1]) = "Other data of interest"
	&& 'FactTable2'[AccountNo] IN VALUES(tmp1[AccountNo])
	),
    "AccountNo", [AccountNo] + 0,
    "AnotherDate", [AnotherDate]
)

-- Join the fact tables together across [AccountNo].
VAR tmp4 = NATURALLEFTOUTERJOIN(tmp2, tmp3)

-- Only interested in data where [AnotherDate] >= [StartDate]
VAR tmp5 = FILTER(tmp4, [AnotherDate] >= [StartDate])

-- For each [AccountNo] and [StartDate], get the earliest date of [AnotherDate]
VAR tmp6 = SUMMARIZE(tmp5, [AccountNo], [StartDate], "FirstAnotherDate", MINX(CURRENTGROUP(),[AnotherDate])

[Greg_Deckler]

@Anonymous - That's kind of odd honestly, I don't suppose you could share some sample data, I'd love to tinker with this.

[amitchandak]

@Anonymous , Another date is used in min expression renamed as FirstAnotherDate, try to use FirstAnotherDate.

[Anonymous]

The field [FirstAnotherDate] does not exist yet. I am trying to create it in VAR tmp6. The SQL equivalent of what I am trying to achieve is this:

SELECT [AccountNo], [StartDate], MIN([AnotherDate]) AS [FirstAnotherDate])
FROM tmp5
GROUP BY [AccountNo], [StartDate]