Forum Discussion
Adjust values based on slicer state
I have a dataset with time series, a value, and a location.
| datetime | value | location |
| 26-2-2019 12:15 | 4 | A |
| 26-2-2019 12:30 | 23 | A |
| 26-2-2019 12:45 | 2 | A |
| 26-2-2019 13:00 | 62 | A |
| 26-2-2019 13:15 | 3 | A |
| 26-2-2019 12:15 | 6 | B |
| 26-2-2019 12:30 | 45 | B |
| 26-2-2019 12:45 | 8 | B |
| 26-2-2019 13:00 | 8 | B |
| 26-2-2019 13:15 | 2 | B |
| 26-2-2019 12:15 | 3 | C |
| 26-2-2019 12:30 | 4 | C |
| 26-2-2019 12:45 | 4 | C |
| 26-2-2019 13:00 | 6 | C |
| 26-2-2019 13:15 | 4 | C |
This data is about electricity meters.
For these time series a have a bar/line chart for the sum(value) of all locations per time interval.
And a bar chart with sum(value) per location.
For analysis purposes, I want to be able to invert the value (value * -1) per location.
Ideally, I can use a standard slicer visual, with a list of all the locations, if a location is unselected the data shown is normal, if a location is selected the data for only the selected locations are inverted (value * -1) and the data is not filtered like the default behaviour of a slicer.
Any ideas?
Hi Anonymous
You may create a measure like below and use it for the datetime visual.
Measure 2 = SUMX(FILTER(Table2,Table2[datetime]=MAX(Table2[datetime])),[invert])
Regards,
Cherie
4 Replies
v-cherch-msftMicrosoft Employee
Hi Anonymous
You may create a slicer table: Slicer = DISTINCT(Table2[location]).Then use it as slicer.Then you may get the measure like below:
invert = IF ( MAX ( Table2[location] ) in VALUES( Slicer[location] ), SUM ( Table2[value] ) * ( -1 ), SUM ( Table2[value] ) )Regards,
Cherie
Thank you. But it doesnt seem to work like i want yet.
In the example pbix you attached;
* if i select no location (ie all values should be original) then all values are * -1. it does work correctly if one or more locations are selected. i managed to fix this myself by putting your code into an IF ISFILTERED
* if i make a column chart with "datetime" on the axis, and "invert" on the values, then it does not behave like i expected. this chart is the ultimate goal of my report. i guess that is because location is not in the chart? is there a way around this?
Bonus question:
The slicer table does not seem to respond to filters. Is that possible? What if there was more data and because of an (external) filter there were less locations?
- v-cherch-msft
Hi Anonymous
You may create a measure like below and use it for the datetime visual.
Measure 2 = SUMX(FILTER(Table2,Table2[datetime]=MAX(Table2[datetime])),[invert])
Regards,
Cherie