Dynamic Measure with Visual Filter - How Power BI works?

[VbOnline]

Hi All,

I have a question with regards to creating a dynamic measure which inturn has multiple measure calculations happenning within it.

The way the dynamic measure is used in the report is, one of the values of the measure is used as "visual filter" while the other value is used as value in the visual. 

The below is the dynamic measure I am referring to:

DynamicScore =
VAR mindate = [minDateACI]
VAR maxdate = [maxDateACI]

VAR Cleanliness =
CALCULATE( SUMX( stn_data, stn_data[score] * stn_data[weight] ) )
/ CALCULATE( SUM( stn_data[weight] ), stn_data[score] <> 0 && NOT ( ISBLANK( stn_data[score] ) ) )

VAR CleanlinessTD =
CALCULATE(
Cleanliness
, DATESBETWEEN(stn_data[srDate]
, mindate, maxdate)
)

VAR Arrival =
CALCULATE( SUMX( stn_data, stn_data[score] * stn_data[weight] ) )
/ CALCULATE( SUM( stn_data[weight] ), stn_data[score] <> 0 && NOT ( ISBLANK( stn_data[score] ) ) )

VAR ArrivalTD =
CALCULATE(
Arrival
, DATESBETWEEN(stn_data[srDate]
, mindate, maxdate)
)

VAR FinalTd =
UNION
(
ROW("ItemName","Cleanliness" ,"ItemValue",CleanlinessTD),
ROW("ItemName","Arrival" ,"ItemValue",ArrivalTD)

RETURN FinalTd

When it comes to visualization, the "Item Name" is used in Visual Filter for a Card and ItemValue is used as the value of that card.

I have two cards, one for Cleanliness and one for Arrival. So, for the respective cards, the visual filter is selected to either Cleanliness or Arrival.

Now, coming to main Question, when Power BI Report runs, the same "dynamic" measure is called for both cards. My understanding is, when the measure is executed, both the measures are "calculated" by Power BI but when it comes to visual filter, out of the measures, it only selects the "filtered" value to show.

Technically speaking, the measures are calcualted twice (once for each card) and filtered to "selected item name". 

Is that the right understanding or Power BI does it differrently?

Thanks,

Vikram

[v-mengzhu-msft]

Hi @VbOnline ,

Do you want to know if measure and filter can be used in combination? Or is it some other issue?

Best regards,

Community Support Team Selina zhu

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

[VbOnline]

@v-mengzhu-msft  The Filter and the Measure will work in combination. I mean to say, the Visual Filter will determine the measure to show in the visual. Based on the filter on the visual, the appropriate measure is used inside the dynamic measure.

Hope this clarifies. Thanks

[VbOnline]

Just to add additional information, when I look at the Query issued by Power BI in Dax Studio or Performance Analyzer, the below is what it looks like:

DEFINE VAR __DS0FilterTable = 
  TREATAS({"Cleanliness"}, 'DynamicScore'[ItemName])

EVALUATE
  SUMMARIZECOLUMNS(
    __DS0FilterTable,
    "MinItemName", IGNORE(CALCULATE(MIN('DynamicScore'[ItemName])))
  )