Trouble using a measure as a filter via VAR in Calculate

[Peter_]

Hi,

I'm trying to create a measure A which runs a measure B within a specific date set by a measure C. Measure C is just a date rank that lets pick a specific date as a rank relative to the latest date (i.e. rank 1 ~ latest date, rank 2 ~ 2nd available date, and so on). So then I can have multiple measures of Measure A with different date ranks specified for different dates so that the output visualization can have multiple columns of measure B for different dates. Something like

Device  |  %GT as of 11/19/2018 | %GT as of 11/20/2018 | %GT as of 11/21/2018

Device1|   ...

Device2|

If I use a date rank calculated via a calculated column instead of the one from Measure C, it works but it's not what I need as I need the report to allow to adjust dates on the page and the dates to be relative to the latest selected.

Measure C: is a date rank to let report select 1st, 2nd, 3rd date going back from the latest.

Date Rank = RANKX( ALLSELECTED(DateTable[Date]), CALCULATE(MAX(DateTable[Date])), , DESC)

Measure B: calculates %GrandTotal of a device within all devices.

[% GT [Devices]]] =

VAR CurrentDeviceCount = [Devices]

VAR TotalDeviceCount = CALCULATE([Devices], ALLSELECTED(DeviceTable))

RETURN

        DIVIDE(CurrentDeviceCount, TotalDeviceCount)

Measure A: calculates %GT for a specific date

[% GT [Devices[Date Rank 2]]]]] =

VAR DateRankValue = 2

VAR TargetDate = FIRSTDATE(FILTER(ALLSELECTED(DateTable[Aggregation Date]),[Date Rank] = DateRankValue))

RETURN

          CALCULATE([% GT [Devices]]], DateTable[Aggregation Date] = TargetDate)

If I replace TargetDate in Measure A with a specific date set via DATE(...) DateRankValue of 2 represents, it works fine. 

Thanks,

Peter

[Ashish_Mathur]

Hi,

Share a sample dataset and also show the expected result.

[Peter_]

I've tested this on a small sample dataset and it works there as expected. Looking again at my main dataset which is ~20M rows, it appears that the problem is that the Measure C doesn't finish to resolve (it's over five minutes after adding it into Values field in Matrix chart and it still spinning).

Am I doing anything blatantly wrong that would make it take so long? Especially, If a version below with hardcoded date works fast, how come the one version with FIRSTDATE(...) doesn't resolve as fast? Is the TargetDate  query called for each row?

Measure A: calculates %GT for a specific date

[% GT [Devices[Date Rank 2]]]]] =

VAR DateRankValue = 2

VAR TargetDate = FIRSTDATE(FILTER(ALLSELECTED(DateTable[Aggregation Date]),[Date Rank] = DateRankValue))

RETURN

          CALCULATE([% GT [Devices]]], DateTable[Aggregation Date] = TargetDate)

Version2: Setting TargetDate with a hardcoded date, the measure calculates quickly (~1-2s to resolve). 

Measure A: calculates %GT for a specific date

[% GT [Devices[Date Rank 2]]]]] =

VAR TargetDate = DATE(2018,11,17)

RETURN

          CALCULATE([% GT [Devices]]], DateTable[Aggregation Date] = TargetDate)