Remove filter on a column but also find selected value

Hoping — Mon, 04 Mar 2024 22:08:53 GMT

I am trying to come up with a tooltip page on a matrix that shows line chart or column chart for all years and highlights the year based on where the cursor is placed ( through conditional formatting).

Sample Data :

The matrix visual looks as below with Year on the COlumn and City on the Row

Measure - SumMeasure = SUM('Fact Table'[Fact Count])

I am creating a tooltip page that displays a line chart for all years 2021 - 2024 (irrespective of slicer filtering) but highlights the year column on which cursor is placed in a different color.

For Example, if I place my cursor second row last column, I should show the column / bar that shows the 2024 value for Philadephia which is 3 in a different color. But there should be 5 columns for 2020, 2021...2024 even though the year slicer is filtered from 2021 to 2024.

This is how far I got: I turned off Keep Filters on the tooltip page. Added the city as a filter card. With Year on x-axis and SumMeasure on y-axis, I am able to get all the years.

Problem: How do I highlight the bar in a different color based on the cursor position. Since I am turning off Keep Filters, year is not flowing through so I do not know where the cursor was placed.

How can I achieve this ?

Re: Remove filter on a column but also find selected value

Jihwan_Kim — Tue, 05 Mar 2024 04:06:10 GMT

Hi,

One of ways to achieve this is to create a disconnected year axis table, something like below.
Please check the below picture and the attached pbix file.

SumMeasure: = SUM(Data[Fact Count])

TREATAS function - DAX | Microsoft Learn

Tooltip SumMeasure: = CALCULATE ( [SumMeasure:], TREATAS ( VALUES ( 'CT Year tooltip axis'[Year] ), 'Year'[Year] ) )

cf Column color: = VAR _yearselect = SELECTEDVALUE( 'Year'[Year] ) RETURN IF ( SELECTEDVALUE ( 'CT Year tooltip axis'[Year] ) = _yearselect, "Red", "Yellow" )

topic Remove filter on a column but also find selected value in DAX Commands and Tips

Remove filter on a column but also find selected value

Re: Remove filter on a column but also find selected value