my current issue is that when I select a specific unit in the table (Visual 3) below, I want only a partial dynamic change in the line and clustered column chart (Visual 2). Specifically, I want the bars to indicate the rent change over time, but I want the line to remain unchanged (the line in Visual 4 is what I am looking for). I’m unsure how to disregard the unit selection just for the measure used in ‘rent change in city’. In my final dashboard, visual 4 would be discarded.
I have tried All, Allexcept, Removefilters, but none of these work. Can someone kindly take a look and help me solve it? thanks!
`This is the measure I created for the bar columns:
Rent change in =
var one_year_filter = FILTER('TABLE', DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) >=0 && DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) <= 12)
var two_year_filter = FILTER('TABLE', DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) >=0 && DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) <= 24)
return
IF(ISBLANK(SWITCH(SELECTEDVALUE('Expiring Month'[Value]),
"Last 12 Months",CALCULATE(SUM('TABLE'[Rent Change in]), one_year_filter),
"Last 24 Months",CALCULATE(SUM('TABLE'[Rent Change in]), two_year_filter),
CALCULATE(SUM('TABLE'[Rent Change in]))
)), blank(), SWITCH(SELECTEDVALUE('Expiring Month'[Value]),
"Last 12 Months",CALCULATE(SUM('Table'[Rent Change in]), one_year_filter),
"Last 24 Months",CALCULATE(SUM('Table'[Rent Change in]), two_year_filter),
CALCULATE(SUM('Table'[Rent Change in]))
))
`This is the measure I created for the line:
Rent Change in by City =
var one_year_filter = FILTER('TABLE', DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) >=0 && DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) <= 12)
var two_year_filter = FILTER('TABLE', DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) >=0 && DATEDIFF('TABLE'[Contr.strt],TODAY(),MONTH) <= 24)
return
IF(ISBLANK(SWITCH(SELECTEDVALUE('Expiring Month'[Value]),
"Last 12 Months",CALCULATE(AVERAGE('TABLE'[Rent Change in]), ALLEXCEPT(DIM,DIM[City]), REMOVEFILTERS('TABLE'[Unit]), one_year_filter),
"Last 24 Months",CALCULATE(AVERAGE('TABLE'[Rent Change in]), ALLEXCEPT(DIM,DIM[City]), REMOVEFILTERS('TABLE'[Unit]), two_year_filter),
CALCULATE(AVERAGE('TABLE'[Rent Change in]), ALLEXCEPT(DIM,DIM[City]),REMOVEFILTERS('TABLE'[Unit]))
)), blank(), SWITCH(SELECTEDVALUE('Expiring Month'[Value]),
"Last 12 Months",CALCULATE(AVERAGE('TABLE'[Rent Change in]), ALLEXCEPT(DIM,DIM[City]), REMOVEFILTERS('TABLE'[Unit]), one_year_filter),
"Last 24 Months",CALCULATE(AVERAGE('TABLE'[Rent Change in]), ALLEXCEPT(DIM,DIM[City]), REMOVEFILTERS('TABLE'[Unit]), two_year_filter),
CALCULATE(AVERAGE('TABLE'[Change in]), ALLEXCEPT(DIM,DIM[City]),REMOVEFILTERS('TABLE'[Unit]))
)
)
thanks so much for your time and help!!!