dynamic benchmark based on filter

[MinCK]

Hello eveyone, 

I am new to Power Bi, may post some stupid question and explanation, but please bear with me. I am learning little by little!

I am working on building benchmarking comparison dashboard on Power BI, using Direct Lake connection. I am aiming to build a barchart where I calculate the differences between my hotel score and other hotels score (based on the slicer selected). I saw some video on Youtube Power BI – Dynamic Benchmarking and Implementing dynamic benchmarking in Power BI which they illustrate my intention. However, they requires me to create a data table specifically for the benchmark, like HotelScore and HotelScore Benchmark. Direct Lake doesn't allow me to create table as easy as other modes. My only solution now is replicating the benchmarking table in SQL and push it the Lakehouse. 

The screenshot shows what I am trying to do, showing all hotels name and, based on whatever hotel I selected, calculating the differences of the hotel score. Surely the hotel name can be shown as a group, region, etc. I did this in Tableau earlier and trying to move to Power BI.

So, my question is do we have any other easier way (DAX code, other setup) allowing me to get the same result without creating the benchmarking table as suggested in the Youtube?

Thank you for your comments in advance!

Best,

Min Chakranont

[v-echaithra]

Hi @MinCK ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[TomMartens]

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