Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a time series line chart that displays data over time for a particular state. I also have a filter that will allow me to filter for cities, so I can see data over time for a particular city instead
what I want to add is another line on my chart for the state, but have it in such a way that it is not affected by my filters.
Basically, my result would be a line chart with 2 lines: 1 line that will show me data to a city(depending on my filter), and another line for the overall state line. That way, I can compare "city" vs overall "state."
I thought adding a new column and aggregating the entire state data would do the trick, but it ended up being literally a flat line.
Solved! Go to Solution.
HI @semidevil
For your 'fixed' line, use a calculated measure and use the CALCULATE/ALL/ALLSELECTED functions to control the effect of external filters on the expression. It's possible to write your calculation so it disregards external filters.
HI @semidevil
For your 'fixed' line, use a calculated measure and use the CALCULATE/ALL/ALLSELECTED functions to control the effect of external filters on the expression. It's possible to write your calculation so it disregards external filters.
Thank you @Phil_Seamark.
Here's the measure I attempted, and when I toggled my city slicer, the line still continue to move even though I expected it to stay at the 'state' level.
Measure= CALCULATE(SUM(Value),FILTER('Table1, Table1[Column1]="variable1"),FILTER(Table2,Table2[Column1]="variable2" || Table2[Column1]= "variable3"),ALL(City[City]))
I tried All, AllExcept, but it still did not seem to work. I want all the rest of the filters to in and out of the query to still work....except the "city" slicer.
EDIT: I just realized that when I removed the "Filter" inside my query, it seemed to solve the problem, and I just had to execute the filter in the GUI (visual level filter). Is this by design? I would like to have at least 1 filter in my query because my end goal is to have 3 line charts: line 1 based on a filter, line 2 based on another filter from the same column, and line 3, the 'state' average.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |