Forum Discussion

awitt's avatar
awitt
Helper III
6 years ago
Solved

Dynamic measures based on multiple slicer selection.

I am looking to calculate a time difference between two status of the same order with the status changing as I need. The idea is that I would have two identical slicers with each of the statuses to c...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    awitt 

    I took a pass at a solution but I have a question.  Right now I am showing the highest date so if you are looking at order 1 ordered date it will show 1/2 but if you look at order 1 with the item it will have a different date for the two items.

    First we make a couple of tables using all available status.  Doint it this way insures we always have the full list.

    Status1 = DISTINCT('Table'[Status])
    Status2 = DISTINCT('Table'[Status])

    I have a couple measures to test the dates that are coming from the slicers (Date1 and Date2)

    And finally a meaure to do the calc.

    DateDiff = 
VAR FirstStatus = SELECTEDVALUE ( Status1[Status] )
VAR FirstStatusDate = CALCULATE(LASTDATE('Table'[TimeStamp]),'Table'[Status] = FirstStatus )
VAR SecondStatus = SELECTEDVALUE ( Status2[Status] )
VAR SecondStatusDate = CALCULATE(LASTDATE('Table'[TimeStamp]),'Table'[Status] = SecondStatus )
RETURN DATEDIFF( FirstStatusDate,SecondStatusDate,DAY)

    I have attached my sample .pbix for you to look at.

     

parry2k's avatar
parry2k
Super User
6 years ago

awitt not sure what you are actually looking for? What is your final goal? What you are trying to achieve?

awitt's avatar
awitt
Helper III
6 years ago

Pretty much what the other commenter got at. Having two seperate slicers containing a large selection of statuses and building measures based on the dynamic selection of those two slicers. So in this example its a DateDiff function based off the two slicers - the difference in time between any selection of statuses. I might need an average or a count of how many times something happened moving forward.

 

The issue is building a model that has a dynamic selection of statuses rather than writing a measure for each one.