Forum Discussion
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 chose from. So in this case "Confirmed" "Ordered" "Delayed" "Allocated" and "Shipped". In slicer 1 I choose "Confirmed" and the other I choose "Shipped" and the result is either an average or some other time between function.
I may want to choose different time between statuses though. So i may want to look at time between Allocated and Shipped and so on. Right now i have dozens of statuses and I have to create a measure for each expression I want to evaluate. This can be literally hundreds of measures... Thoughts?
| Order Number | Item | Order Number Item | Status | TimeStamp |
| 1 | A | 1A | Confirmed | 1/1/2019 |
| 1 | A | 1A | Ordered | 1/1/2019 |
| 1 | A | 1A | Delayed | 1/2/2019 |
| 1 | A | 1A | Allocated | 1/8/2019 |
| 1 | A | 1A | Shipped | 1/9/2019 |
| 1 | B | 1B | Confirmed | 1/2/2019 |
| 1 | B | 1B | Ordered | 1/2/2019 |
| 1 | B | 1B | Allocated | 1/3/2019 |
| 1 | B | 1B | Shipped | 1/10/2019 |
| 2 | A | 2A | Confirmed | 1/1/2019 |
| 2 | A | 2A | Ordered | 1/1/2019 |
| 2 | A | 2A | Delayed | 1/2/2019 |
| 2 | A | 2A | Allocated | 1/8/2019 |
| 2 | A | 2A | Shipped | 1/9/2019 |
| 2 | C | 2C | Confirmed | 1/2/2019 |
| 2 | C | 2C | Ordered | 1/2/2019 |
| 2 | C | 2C | Allocated | 1/3/2019 |
| 2 | C | 2C | Shipped | 1/10/2019 |
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.
9 Replies
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.
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.
jdbuchanan71 Thanks for the response! I am getting this error in my model though. This is just with the Date1 measure.
I don't know why you are getting that error. This measure works for me and there are duplicate dates in the table.
LastDate = LASTDATE ( 'Table'[TimeStamp] )
Do you get the error with this measure? If so, can you share your .pbix file?
