topic dynamically exclude records based on user defined conditions in DAX Commands and Tips

dynamically exclude records based on user defined conditions

Michele_I — Tue, 30 Jan 2024 18:19:48 GMT

Hi all, and thank you for any suggestion with this little issue.

This is a semplified example, but my real model works the same way.

I have a projects table (every row is a project) and a Localizations table, in which every project has one row for each municipality involved.

The user can select one or more municipalities (Localizations[Municipality]) using a slicer.

I'm looking for calculating the n. of projects and total budget of those projects localized only in one or more of the municipalities the user selected, but not in any other municipality not selected by the user.

Examples when the user choses municipalities A, B and C:
Project 1 localized in municipality A: included
Project 2 localized in municipality B: included
Project 3 localized in municipalities A and B: included
Project 4 localized in municipalities A, B and D : excluded
Project 5 localized in municipalities D and E: excluded

I manage to calculate the n. of projects and their budget localized in at least in one of the municipalities the user selected, but this would include projects 1, 2, 3 and 4 of the example, while i need project D being excluded as it is localized also in municipality D which is not in the list the user selected.

thank you very much, any help is really appreciated.

Michele

Re: dynamically exclude records based on user defined conditions

Greg_Deckler — Tue, 30 Jan 2024 18:45:47 GMT

Michele_I Maybe something like this. See PBIX attached below signature.

Selection Measure = 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) RETURN __Result

Re: dynamically exclude records based on user defined conditions

Michele_I — Tue, 30 Jan 2024 20:52:27 GMT

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!

Re: dynamically exclude records based on user defined conditions

Greg_Deckler — Tue, 30 Jan 2024 21:00:33 GMT

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

Re: dynamically exclude records based on user defined conditions

Michele_I — Tue, 30 Jan 2024 21:26:10 GMT

Greg_Deckler Your way is much better 🙂

Thank you very much!