Hi,
I have a client request regarding to have two slicer controls, each one can update a calculated measure separately. Two measures is being used in a line and clustered column chart, one used for bars, one for the line.
I've created two slicers which are in a hierarchy.
Filter 1: Table[Continent], Table[Country]
Filter 2: Table[Continent], Table[Country], Table[City]
I use
Filter 1 to control the bars, here is my DAX:
Table[Max Order] =
IF(
ISFILTERED(Table[Continent]) ||
(ISFILTERED(Table[Continent]) && ISFILTERED(Table[Country])),
CALCULATE(
MAX(Table[Order]),
ALL(Table)
)
I use Filter 2 to control the line:
Table[Average Order] =
IF(
ISFILTERED(Table[Continent]) ||
(ISFILTERED(Table[Continent]) && ISFILTERED(Table[Country])) ||
(ISFILTERED(Table[Continent]) && ISFILTERED(Table[Country]) && ISFILTERED(Table[City])),
CALCULATE(
AVERAGE(Table[Order]),
ALL(Table)
)
Expected result:
select the values North America, Canada from Filter 1, select Europe, UK, London from Filter 2. The bars calculate the max order number in Canada, the line gives a trend that represents the average number of orders in London city.
Currently my line and clustered column chart just goes blank, I know there must be a conflict as I selected across two different regions but anyone knows how to deal with this situation? Thank you!
