Use variable as Filter ?

[Cookistador]

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

Name	amount	Condition
Dog	5	'Table'[Column1] = "Dog"
Cat	11	'Table'[Column1] = "Cat"
Horse	2	'Table'[Column1] = "Horse"
Wolf	1	'Table'[Column1] = "Wolf"

And this is how I would like to use this table

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

return

Calculate(Count(Table[Count],Filtering)

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

[BA_Pete]

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!

Pete

[Cookistador]

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

[BA_Pete]

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.

Pete