Static VAR filter for calculated columns

[DouweMeer]

 Anyway you can store your VAR filter somewhere so it can be used by another calculated column as well? 

VAR a1 = stuff1

Column 1 = calculate ( stuff2 , filter ( a1 )

Column 2 = calculate ( stuff3 , filter ( a1 )

It is a bit annoying to go through all your columns to modify your filter when you want to add something. 

[Mariusz]

Hi @DouweMeer 

You can create a table that will hold you criteria like in the example below.

My Favourite Colors = {"Azure", "Red"}

Then you can use it in your Measurs or Columns like in the example below.

# my favourite colors = --this is a Measure
CALCULATE(
    COUNTROWS( 'Product' ),
    KEEPFILTERS( 'Product'[Color] IN 'My Favourite Colors' )
)

Regards,
Mariusz

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

[DouweMeer · Accepted solution]

@Mariusz 

You could indeed use a measure to find independently of the context a fixed scalar value from a different table and use that as a static validation value which you can you across multiple calculated columns.

Gives me a good idea. I think I already know how I can sail past the lack of its functionality. If I create a custom table with a 1:1 relationships to the main table, I can create my calculations inside the selectcolumns thus combining all expression of all calculated columns in 1 single DAX expression.

Just have to look for an easy way to find back what I wrote so I can easily fix an issue or add something so it will be more effective...

[v-shex-msft]

HI @DouweMeer ,

Unfortunate, you can't invoke variable from external calculate column. BTW, power bi not support to create dynamic calculated column/table based on filter/slicer.

Regards,

Xiaoxin Sheng

[DouweMeer]

Hello @v-shex-msft ,

I wasn't curious to whether filter might affect calculated column/ tables. But whether I could use a string value from a different column to use in my DAX expression itself. 

It is like create a Number for a product which is then stored in another table. But the Product table is leading in its Id and thus also for its Number. When you change the Number in the product table, it will also change the rest of the tables where the related Id's contain the Product Number.

This method of updating data is something I was hoping I could apply to the DAX language itself to make the maintenance of my data easier. Then when I would change the filter in the 'main table', all calculated columns contain that filter, will change as well.

[v-shex-msft]

Hi @DouweMeer ,

>>When you change the Number in the product table, it will also change the rest of the tables where the related Id's contain the Product Number.

Yes, you can invoke different table fields in one dax formula(include current and other tables).

>>This method of updating data is something I was hoping I could apply to the DAX language itself to make the maintenance of my data easier. Then when I would change the filter in the 'main table', all calculated columns contain that filter, will change as well.

As I said, data view level filters(slicer/filters) not real filter data model table records.
So if you create calculate column, its calculate result not dynamic changes when you modify data view filters.(these calculated result changes when datasource or query editor filter changes)

Regards,

Xiaoxin Sheng