Measure that will return count of ids for each value based on max date in history table

Anonymous — Thu, 14 Nov 2019 21:43:56 GMT

I am trying to create a measure that will count the number of ids that held a certain value prior to a selected day. Rows in my fact table are only updated when the value changes so I need the user to be able to select a day and get a view of the value as of the most recent update.

So far this is what I have:

I have a history/fact table ('historyTable') and a disconnected date table ('dateTable') as follows:

'dateTable' (disconnected):

date

11/14/2019

11/13/2019

...

3/21/2015

1/1/2015

'historyTable':

Id	dimension	dimSelected	dimDate
A	Team	Green	7/10/2019
A	Team	Blue	4/14/2019
A	Team	Red	10/29/2018
B	Team	Purple	9/23/2019
B	Team	Red	1/3/2019
B	Team	Black	10/12/2018
A	Rep	Mark	7/10/2019
A	Rep	Joe	4/14/2019
A	Rep	Max	10/29/2018
B	Rep	Sally	9/23/2019
B	Rep	Sue	1/3/2019
B	Rep	Bob	10/12/2018

The slicer on the 'dimension' is set up so that a user selection is always required, so for this example let's imagine the user has selected 'Team', resulting in the historyTable data filtered down to only the 'Team' dimension

'historyTable' (filtered by dimension = Team):

Id	dimension	dimSelected	dimDate
A	Team	Green	7/10/2019
A	Team	Blue	4/14/2019
A	Team	Red	10/29/2018
B	Team	Purple	9/23/2019
B	Team	Red	1/3/2019
B	Team	Black	10/12/2018

I am trying to create a measure that can be dropped into a table with 'historyTable'[dimSelected] that will count the distinct number of Ids with the dimSelected value based on the max date

So with 11/14/2019 selected from dateTable[date] and 'Team' selected from the dimension filter the following counts should be returned

'historyTable'[dimSelected]	[idCount]
Green	1
Blue	0
Red	0
Purple	1
Black	0

But when the user changes the date selection to 6/30/2019 for example, the measure should recalculate to return the following counts

'historyTable'[dimSelected]	[idCount]
Green	0
Blue	1
Red	1
Purple	0
Black	0

So far I have the following measure that based on the date selected seems to calculate the max dimSelected value correctly when dropped into a table with historyTable[id] as the row context

[lastValue] =
VAR viewDate = SELECTEDVALUE(dateTable[date])
VAR maxUpdate = CALCULATE(MAX(historyTable[dimDate]), FILTER(historyTable, historyTable[dimDate[ <= viewDate))
VAR maxValue = CALCULATE(LASTNONBLANK(historyTable[dimDate], [dimDate]), historyTable[dimDate]<=maxUpdate)
RETURN
MAXX(FILTER(ALLSELECTED(historyTable), 'historyTable'[dimDate] = maxValue), 'historyTable'[dimSelected])

The above measure provides the following when dropped into a table with id values which I believe is the expected output as far as calculating the most up-to-date value based on date selected

'historyTable'[id]	[lastValue]
A	Green
B	Purple

When the date selection changes to 6/30/2019 the measure recalculates and returns the following

'historyTable'[id]	[lastValue]
A	Blue
B	Red

I am having trouble with the next step that will translate [lastValue] to a measure that will calculate counts for dimSelected.

I have tried the following but when I drop it into a table with 'historyTable'[dimSelected] values I get a count for each value that the id ever held rather than a count for the max value based on date selected

[idCount] =
VAR maxValue = [lastValue]
RETURN
CALCULATE(COUNTROWS(VALUES('historyTable'[Id])),

FILTER(ALLSELECTED(historyTable), 'historyTable'[dimSelected] = maxValue)

This measure currently returns the following in a table with dimSelected values and 11/14/2019 selected from dateTable[date]:

'historyTable'[dimSelected]	[idCount]
Green	1
Blue	1
Red	2
Purple	1
Black	1

Is there a way to adjust my [idCount] measure so it only returns the count for the max value for each id according to the date selected?

Re: Measure that will return count of ids for each value based on max date in history table

Anonymous — Fri, 15 Nov 2019 21:29:24 GMT

Update: I think I am getting closer, but still not quite there. I created a new measure that generates a table as a variable with the most up-to-date values for each Id based on the date and dimension selected.

idCount =

VAR selectedDate = SELECTEDVALUE(dateTable[date])

VAR selectedAxis = SELECTEDVALUE(historyTable[dimension])

VAR __table =

ADDCOLUMNS(SUMMARIZE(historyTable, historyTable[Id],

"maxDate" = CALCULATE(MAX(historyTable[dimDate]),

FILTER(historyTable, historyTable[dimDate]<=selectedDate

&& historyTable[dimension] = selectedAxis))),

"maxValue", MAXX(FILTER(historyTable, [dimDate] = [maxDate]

&& historyTable[dimension] = selectedAxis), historyTable[dimSelected])

RETURN

CALCULATE(SUMX(__table,

IF(maxDate = CALCULATE(MAX(historyTable[dimDate],

FILTER(historyTable, historyTable[dimDate]<=selectedDate)), 1, 0)),

ALLSELECTED(historyTable),

FILTER(__table, [maxDate] <= selectedDate))

but I am still not getting the counts I am expecting when dropping the measure into a table with the values from 'historyTable'[dimSelected]

topic Measure that will return count of ids for each value based on max date in history table in DAX Commands and Tips

Measure that will return count of ids for each value based on max date in history table

Re: Measure that will return count of ids for each value based on max date in history table