Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |