Filter using values in a table

[wl2020]

So I created a table to filter the calculate results. The table looks like this:

CALCULATE(SUM(LN_Dim_Account_Value[value]); LN_Dim_Account_Value[UID] IN {[lnDimUID]})

The idea is that the user will click on a dropdown menu in a slicer, and then the formula calculates the sum of the value column based on the column IDs I provide in the table. The formula works for all items with a single column ID, but when there are more than one (the one separated by ";"), the formula stops working. 

Any idea how to make the formula work for cells with more than one value?

[Anonymous · Accepted solution]

Hi.

You should never store non-atomic entities in one field. If you have the above situation, you have to create a mapping (a bridge table) between your records and all the ID's that are present in your string for that record. You have a Many-2-Many situation here.

Best
D

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[wl2020]

I apologize. What should I do to improve the post? I can't post the whole table unfortunately but am happy to include more details.