Filtering measures with a slider

[wilson_smyth]

I have a table visualization. One of the measures (Total Budget) is filtered via a slider parameter. Rows in the table visualization disappear as the slider is increased.

Code for this measure is:

Total Budget = calculate(sumx('fact','fact'[Total]), 
filter('fact', [Total] >= 'Total Filter'[Total Filter Value]))

This works fine, until i add another measure, at which point all that happens Total Budget column turns blank, but the rows do not disappear,  is  if the slider is moved.

The only way I have found around this is to do an if statement on all other measures that will be on the table visualization, for example:

SubTotal Budget x2 = if(ISBLANK([Total Budget]), BLANK() ,calculate(sumx('fact','fact'[Subtotal])* 2) )

This ensures that the measure [SubTotal Budget x2] disappears when the [Total Budget] value associated with that row is filtered.

I have two questions:

- is this the best way to filter a table visualization whern there are multiple measures, but only one is being filtered?

- is there an easier way to do this, instead of altering all the measures to reference [Total Budget] ?

The sample pbix is located here.

Thanks for any help or advice.

[v-xuding-msft]

Hi @wilson_smyth ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue

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

[v-xuding-msft · Accepted solution]

Hi @wilson_smyth ,

The scenario is caused by there is no relationship among the table 'Total Filter' and other tables.  The measure can be filtered because the table is used in it. 

I modified the formula of Total Budget x 3 you can have a try.

Total Budget x 3 = IF(ISFILTERED('Total Filter'[Total Filter]),calculate(sumx('fact','fact'[Total])* 3,FILTER('fact','fact'[Total] >= MIN('Total Filter'[Total Filter]))))

Best Regards,

Xue Ding

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