Forum Discussion
dynamically exclude records based on user defined conditions
Michele_I Well, if it works, it works. Probably not how I would have done it. I would have probably done something like:
Budget OK = VAR __Table = FILTER( ADDCOLUMNS('Projects', "__Selected", [Selection Measure]), [__Selected] = 1) VAR __Result = SUMX(__Table, [budget]) RETURN __Result
Greg_Deckler thank you very much for helping me.
Thanks to your code i could get the table you see on the left, which correctly associates value 1 to projectIDs matching the requirements. Budget total is 100 but if i filter the table for "selection measure" = 1 i correctly get 60. I would never get here without your help!
This all works fine in a table with one record per project, but how can i use your code to build a measure that automatically calculates a total budget of 60 when municipalities A and B are selected?
To achieve this i tried adding some more code (it seems fine to me but i'm not sure):
budget ok = VAR __Municipalities = SELECTCOLUMNS('Municipalities', "municipality", [municipality])
VAR __ProjectID = MAX('Projects'[projectID])
VAR __Localizations = SELECTCOLUMNS(FILTER(ALL('Localizations'), [projectID] = __ProjectID), "__municipality", [municipality])
VAR __Except = EXCEPT(__Localizations, __Municipalities)
VAR __Intersect = INTERSECT(__Localizations, __Municipalities)
VAR __Result = IF(COUNTROWS(__Except) = BLANK() && COUNTROWS(__Intersect) <> BLANK(), 1, 0)
var __projecttable = ADDCOLUMNS(Projects,"Check",[Selection Measure])
var __filteredprojecttable = FILTER(__projecttable, [Check]=1)
return
CALCULATE(SUM(Projects[budget]),__filteredprojecttable)
Is this the right way to use your tips?
Thank you very much!
Community ChampionMichele_I Well, if it works, it works. Probably not how I would have done it. I would have probably done something like:
Budget OK =
VAR __Table = FILTER( ADDCOLUMNS('Projects', "__Selected", [Selection Measure]), [__Selected] = 1)
VAR __Result = SUMX(__Table, [budget])
RETURN
__ResultGreg_Deckler Your way is much better 🙂
Thank you very much!