Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II
Helper II

Use variable as Filter ?

Hello everyone,


I'm facing to a weird issue, I have some measures with very long filtering conditions, so I'm trying to store these conditions in a table and then called them when I need

So just to clarify the sitation, this is the kind of table I'm using

Dog5'Table'[Column1] = "Dog"
Cat11'Table'[Column1] = "Cat"
Horse2'Table'[Column1] = "Horse"
Wolf1'Table'[Column1] = "Wolf"


And this is how I would like to use this table

CATCounting = VAR Filtering = LOOKUPVALUE(Table[Condition],Table[Name],"Cat")



But here, my measure returns 

Something's wrong with one or more fields: (Table) Measure: The True/False expression does not specifiy a column. Each true/false epxression used as a tabme filter expression must refer to exactly one column


I tried many things but nothing gives me the results I'm execpting for

Any suggestion or workaround to be able to use this table?


Thank you for your help


Super User
Super User

Hi @Cookistador ,


I'm not sure you're going to get much joy with this method. CALCULATE filters need a column reference.

If you have a lot of measures like Sales, Quantity, Hours etc. and you need to have a version of each of them for Cats, Dogs, Horses etc. then I think the way to go would be Calculation Groups.


Instead of creating hundreds of measures like [Cat Sales], [Dog Sales] and so on, you can just create [Sales] and use Calculation Items over the top.


It's not difficult to set these up, but it's quite a lot for me to cover here, so check out these links and see what you think:

Patrick @ Guy in a Cube

Marco Russo @ SQLBI


These links, and any others around, are much of a muchness, but should get you started on the right path.

Once you've got Tabular Editor and Calculation Groups set up for this solution, you won't understand how you ever managed without them!



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Thank you for your answer, I think my example was not clear enough

In fact, the idea behind that would be to modify the business rules on the go

I have a lot of measures whichs are using a lot of filters, for example ten measures are using the following rules: ( in average, measures need more than 50 filters)

"ChargeType[ChargeType] ="Fees"
|| ChargeType[ChargeType] ="Director fees"
|| ChargeType[ChargeType] ="Sales fees"
|| ChargeType[ChargeType] ="Eaton Moeller"
|| ChargeType[ChargeType] ="Siemens"
|| ChargeType[ChargeType] ="Partner Pierre Dupont"
|| ChargeType[ChargeType] ="Partner Maxime duhaut"
|| ChargeType[ChargeType] ="Partner Martin Martin"
|| ChargeType[ChargeType] ="Partner Marc Dusseldorf"
|| ChargeType[ChargeType] ="Partner Gilles Chafouin"


But if in 1 week, I want to modify my filter to remove and/or add new filters, I have to go in my cube model and Updated all of these fields


To have something easier to maintain, I created a small program wich is able to modify my query in SQL database, I would like to use this SQL results to avoid to manually update all measures


I hope it is more clear for you 


Once again, thank you for your help and I think calculation group could be usefull for another application I have

Hi @Cookistador ,


It sounds like this is exactly what Calculation Groups would help with, but obviously I don't know your scenario as well as you do.

Another option would be to identify the common features of your measure filters (i.e. what do "Fees", "Director fees", "Sales fees" etc. have in common) then create a column in Power Query that assigns these all to a single group. You could then just filter your measures with ChargeType[chargeGroup] = "Group A", and you'd only need to update the group column once in order to propogate the changes to all measures using that filter. You could also create these groups in a table that is separate if there's values that cross multiple groups. As long as it can be related back to the main fact table it should work in a similar way.

It may need a little more detail than my basic example, but the principle is sound.



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors