Highlighting Selected Column in a Bar Chart Problem

[WishAskedSooner]

I know this is a fairly common request and the Youtube video below works as expected:

https://www.youtube.com/watch?v=FnEKyhSf2mM 

However, there is one big caveat. On my report, I have numerous visuals/measures that ARE driven by the date slicer on the page. Therefore, if I use the unconnected Dim Date table in the slicer, it breaks every visual, and I don't want to have to re-design the entire page/measures in order to highlight the selected column for this single bar chart. I have tried to engineer a solution that keeps the connected Dim Date table on the slicer, but I can't get it to work.

I have a fairly simple measure that is driving the bar chart:

CALCULATE(SUM('Fact'[Val]), ALL('Prod'), 'Prod'[ID] = 1)

So, for example, if the selected month in the connected Dim Date table slicer is January, then the above measure correctly returns $100. However, now I need it to return the proper value for each Month in the bar chart with the X-axis set to the Month field from the unconnected Dim Dates table. If I leave as is, it simply returns $100 across all dates in the bar chart.

I have tried inserting TREATAS to filter on the unrelated Dim Date table:

CALCULATE(SUM('Fact'[Val]), ALL('Prod'), 'Prod'[ID] = 1, TREATAS(VALUES('UnDimDates'[Month]), 'DimDates'[Month]))

but all I get is a subset of the values which seems to correspond to the quarter that the selected month resides in, and blanks for the rest of the months. See below:

So, I am trying to figure out why I don't get the values for every month. Also, the Month column in the table above is from the unconnected Dim Dates table, the Sum column is the Measure above with TREATAS, and the Color column is the following measure:

Color = IF(ISFILTERED('DimDates')
        && VALUES('UnDimDates'[Month]) IN VALUES('DimDates'[Month])
        , "Green", "Grey")

FWIW, my Dimension Date table is not a Date table proper because this Data Model is only First of the Month, not every date, and it is a native query from SQL Server. Furthermore, the [Month] field is actually a custom format of the [Date] field because I can't stand the default date formats in PBI. This, of course, introduces further wrinkles in getting PBI to order this field "chronologically" versus alphabetically, so I have a third Sort column. Not sure if any of that matters, though.

Any ideas on how to modify my measure above to work properly with the unrelated Dim Dates table in the bar chart? Thanks!

[WishAskedSooner · Accepted solution]

I found the solution:

CALCULATE(SUM('Fact'[Val]), ALL('Prod'), 'Prod'[ID] = 1, ALL('DimDates'[Month]), TREATAS(VALUES('UnDimDates'[Month]), 'DimDates'[Month]))

[v-linyulu-msft]

Hi,@WishAskedSooner 

Regarding the issue you raised, my solution is as follows:

1.First, you want to make sure that there is no active relationship between your DimDates and UnDimDates tables. This setting is essential for the TREATAS () function to work as intended, because it actually applies the filter context from one table to another without the need for a physical relationship. TREATAS () is best used when no relationship exists between tables. If there are multiple relationships between tables involved, consider using the USERELATIONSHIP () function instead.

And if the value returned in the table expression does not exist in the column, the value is ignored.

Here is a screenshot of the official document:

Here is the relevant documentation:
TREATAS function - DAX | Microsoft Learn

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

2.Second, according to the case provided in the official documentation, you can try checking your dax to make sure that the VALUES function is used on slicer connected tables (DimDates) and that the second argument of TREATAS is a column from an unconnected table (UnDimDates).

Thank you again for providing a detailed description of your problem. However, we cannot provide better suggestions and help in the existing information you provide, so we hope you can provide more detailed data information, such as the relationship between tables and data composition, which will be beneficial to our better service for you. And please pay attention to removing sensitive information.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.