March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
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:
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
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.
Pete
Proud to be a Datanaut!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
16 | |
16 | |
11 |