dynamic benchmark based on filter

Hey MinCK ,

If your data visualization must show all hotels, you need the additional table because otherwise, only the selected table will appear.

It's important to understand that DAX does not allow you to show or hide categorical values (like hotels) inside a visualization, even though it sometimes seems possible. When talking about measures, it's possible to manipulate the current filter context that is applied before the numerical expression is evaluated (in my simple example below, it's the averaging of the SalesAmount). For this reason, there are two lines in the line chart, even if only color is selected:

The DAX of the measure "":

SalesAmount (ms) - Comparison = 

var tableOfAllColorsExceptSelected = 
    EXCEPT( 
        CALCULATETABLE( VALUES( 'DimProduct'[ColorName] ), REMOVEFILTERS('DimProduct'[ColorName] ) ) , 
        VALUES( 'DimProduct'[ColorName] )  
    ))
return

AVERAGEX(
    tableOfAllColorsExceptSelected,
    [SalesAmount (ms)] 
)

The important part is the EXCEPT( ... ) because it returns all the colors that are not selected, allowing me to calculate the average value.

If you require showing all colors in the visual (regardless of whether we are talking about a line chart or a table visual) but need to select one color/hotel, then you need the additional table.

Regarding the Direct Lake connection mode, be aware that adding a table to your semantic model by creating a view in your lakehouse will force the semantic model to fall back to Direct Query mode, which means the lakehouse needs an extra table.

Hopefully, this adds some new insights.

Regards,

Tom