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?
