Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamically show the latest value of Items in a selected date range in another slicer.

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

 

 

 

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

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:

sturlaws_0-1596551320107.png

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

 

nandic
Memorable Member
Memorable Member

Hi @Anonymous ,
Here is example of calculated measure which returns last name in my demo:

Measure =
var maxDate = MAXX(ALLSELECTED(Sheet1[Date]),Sheet1[Date])
RETURN
CALCULATE(LASTNONBLANK(Sheet1[Item Name],Sheet1[Item Name]),Sheet1[Date]<=maxDate)

It returns last "Name" in selected period of time. As measure can't be used in slicer, we can do a little hack, we will create a list by item id and add this measure. For each item id it will return last item name.
Now as we can't hide item id we can just insert shape and set fill color to white or change transparency. 
In screenshot below i set transparency and border to this inserted shape - in real life this shape will hide item id.

Result:
last selected date slicer.PNG

Not the most beautiful solution, but it might help.

Cheers,
Nemanja

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors