Help with creating a Dynamic Lookup Tool in Power BI

[Chinny1]

Hi everyone.

I have 2 tables:

Table 1

Machine ID	Part Type
001	Gears
001	Wheels
001	Diesel Engine
002	Gears
002	Wheels
002	Diesel Engine
002	Valves
002	Pistons
003	Valves
003	Wheels
003	Pistons

Table 2

Part ID	Part
101	Gears
102	Wheels
103	Diesel Engine
104	Valves
105	Pistons

I am trying to build a tool where I am able to input or select (via a Slicer):
1) A Machine ID
2) Parts I want to subtract from the Machine
3) Parts I want to add to the Machine

and produce an output where I am able to identify what machine that is entirely composed of those parts (if any), or return a "Machine does not exist" if there is no machine that matches that exact combination of parts.

For example, if I select:
1) Machine 003
2) Parts to subtract: Valves, Pistons
3) Parts to add: Diesel Engine, Gears

I would get an output of 001.

Appreciate any help on if this is feasible in Power BI and if so, how I can achieve this. In general, to action the "subtraction", I had an idea of creating an additional column where the Part would be replaced by a blank if it were selected. I would then concatenate all the Parts, grouped by Machine. However, I haven't managed to successfully translate this into DAX

[danextian · Accepted solution]

This proved to be tricky. Not selecting anyting from add or remove tables means selecting all values so there must some kind of a logic to replace the existing values with a dummy one if none is selected at all.  I also realized that counting the parts in the new parts list and comparing it with the current count per machine is unreliable as depending on the combination of parts, the count for both could be the same and I coudn't find any formula that would compare against a table of values and return true if all values from the first table match the second table.  As a workaroud i compared the concatenated new list of parts with t hat of the o utput table.

[Chinny1]

Hi @danextian , I'll need some time to look over and digest your code in more detail, but from a brief review it works perfectly.

Your solution was also very instructive to me personally in how to manipulate data within Power BI. My intial idea for comparison of the current output and the original machine input was that, I could concatenate the parts, then sort that string alphabetically (which seems to not be possible in DAX), but somehow it did not occur to me to sort the Part Types column first, then concatenate it.

Fantastic work and thanks again for the help

[danextian]

I just realized that another way to check if the parts from the output complete match the new list is using both INTERSECT and EXCEPTP at the same time.

Check2 = 
VAR __CURRENT =
    SELECTCOLUMNS ( VALUES ( 'Machine Input'[Part Type] ), "Part", [Part Type] )
VAR __DUMMY =
    ROW ( "Part", "A" )
VAR __ADD =
    VALUES ( 'Parts to Add'[Part] )
VAR __ADD2 =
    FILTER (
        UNION ( __ADD, __DUMMY ),
        IF ( NOT ( ISFILTERED ( 'Parts to Add' ) ), [Part] = "A", [Part] <> "A" )
    )
VAR __SUB =
    VALUES ( 'Parts to Remove'[Part] )
VAR __SUB2 =
    FILTER (
        UNION ( __SUB, __DUMMY ),
        IF ( NOT ( ISFILTERED ( 'Parts to Remove' ) ), [Part] = "A", [Part] <> "A" )
    )
VAR __CURRENT_ADD =
    UNION ( __CURRENT, __ADD2, __DUMMY )
VAR __REMOVED =
    FILTER ( EXCEPT ( __CURRENT_ADD, __SUB2 ), [Part] <> "A" )
VAR __UPDATEDLIST_COUNT =
    COUNTROWS ( __REMOVED )
VAR __ALL_PARTS_BY_MACHINE =
    VALUES ( 'Machine Output'[Part Type] )
RETURN
    COUNTROWS ( INTERSECT ( __ALL_PARTS_BY_MACHINE, __REMOVED ) ) = __UPDATEDLIST_COUNT
        && COUNTROWS ( EXCEPT ( __ALL_PARTS_BY_MACHINE, __REMOVED ) ) = 0
//all parts in output should be present in the new parts list and no part should be left if all from the new list are moved from the output

[danextian]

Hi @Chinny1 ,

You will need to duplicate both of your original tables either in DAX or M so that would be four in total. All these tables are disconnected from one another and then create measures to show the machine ID based on what's bee selected to be added and removed from the original   parts list as per machine ID selected.

Please see attached pbix for details.

[Chinny1]

Hey @danextian , thanks for this, very impressed at how elegant your solution was. Seems like I was way off on my solution. There are some edge cases that this does not work well for though.

For example,

1) if there is a need to only add part(s) but not subtract, or vice-versa, the result does not show as intended. 
When Machine 001 is selected, Add Pistons and Valves, Subtract Nothing, I would expect to see 002, but the Output does not show an answer

2) Also, for some reason, it also shows Machines which have matching existing parts even if it is not an exact match.

I appended this additional Machine to illustrate:

Machine ID	Part Types
005	Pistons
005	Valves

E.g. Select Machine 004, Add Wheels, Subtract Diesel Engine. I would expect to see only Machine 003, but Machine 005 pops up in Output as well.

Once again, greatly appreciate any help you can provide on these issues.