Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I need a help with one DAX Scenario. I have an item table in which the item names can be changing without changing its id. I need to show an item slicer with the latest value of the items only according to the slicer values in the date.
Scenario :
My Item name is changed from A to B on 5 Jan 2020, B to C on 15 Jan 2020 and C to D on 18th Jan 2020. ItemId remains the same for this item and let's assume it's 1.
An another item, itemid 2 is also there. This items name from X is changed to Y on 3 Jan 2020 and then to Z on 14 Jan 2020.
Now on my relative date slicer if I select 1 Jan 2020 to 20 Jan 2020, we need to represent only D & Z in an another slicer that is Item slicer. This is because these are the latest values according to the date selection.
If I select 10 Jan - 15 Jan, then the values in slicer would be C & Z.
Hope I explained the scenario correctly and could someone help with this please.
Regards,
Jishnu
Hi @Anonymous
you will need to have columns for validFrom and validTo. The change of date will be the validTo column, while the validFrom column is computed like this:
ValidTo =
VAR _item = 'Table'[itemID]
VAR _validTo = 'Table'[ValidTo]
VAR _validFrom =
CALCULATE (
MIN ( 'Table'[ValidTo] ),
FILTER (
ALL ( 'Table' ),
'Table'[itemID] = _item
&& 'Table'[ValidTo] < _validTo
)
)
RETURN
IF ( ISBLANK ( _validFrom ), DATE ( 1900, 1, 1 ), _validFrom - 1 )
Next create a measure like this:
Measure Filter =
VAR _selectedDate =
MAX ( Dates[Date] )
RETURN
COUNTROWS (
FILTER (
'Table',
'Table'[ValidFrom] <= _selectedDate
&& 'Table'[ValidTo] >= _selectedDate
)
)
Create a slicer with the item names, and add this measure to the filter of that slicer like this:
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi @Anonymous ,
Here is example of calculated measure which returns last name in my demo:
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |