cancel
Showing results for
Did you mean:
Helper II

## IF with columns from differents table

Hey, everyone!

It's me again haha

So, thats my problem now:

I have the following tables:

Table CL

 Order Line O+L Qty Boxs Reason Week 12345 1 12345-1 12 1095 16 12345 2 12345-2 41 1095 16 56789 1 56789-1 10 1095 16 54331 1 54331-1 5 1095 16 54331 2 54331-2 1 1095 16 54331 3 54331-3 54 1095 16 91845 1 91845-1 32 1095 16 12141 1 12141-1 16 1095 16

Table SAP:

 Order Line O+L Delivery Product Pendin 12345 1 12345-1 169823878 67691987 12 12345 1 12345-1 169120805 67691987 12 12345 1 12345-1 169015632 67691987 12 12345 2 12345-2 169154135 68290583 41 12345 2 12345-2 169154135 68276046 41 56789 1 56789-1 169749494 68259897 10 54331 1 54331-1 169679810 67815790 5 54331 1 54331-1 169749580 67815790 5

And then i created a new tables to help me to link both tables, CL and SAP:

Table O+L:

 O+L 12345-1 12345-2 56789-1 54331-1 54331-2 54331-3 91845-1 12141-1

So heres my relantionships:

note: please ignore table Orders and Lines and PL = O+L

Now what i need is just compare the columns Pending (from table SAP) and Qty Boxs (from table CL), like if(Qtd Box = Pending, "ok", "nok")

I tried (probably i did it all wrong haha):

- Create a column on table SAP with  IF(Pending = CALCULATE(SUM('CL'[QTY BOXS]), 'CL'[REASON] = "1095" && 'CL'[WEEK] = "16" ), "ok", "nok") - it didnt work because the calculate return the sum of all QTY BOXS in CL i believe (i dont exactly what is the return but its a huge number)

- Create a column in table SAP with the values of "QTY BOXs" using Related but the only options to use Related is the table "O+L"

- Column and measure with LOOKUPVALUE('CL'[QTY BOXs], 'CL'[O+L], SAP[O+L]) but i got the error: A multi-value table was provided, with a single value expected.

- I created a measure SUMX('CL', 'CL'[QTY BOXS]) and it returns the correct value from "QTY BOXS" but when i try to use it on IF, i dont get the correct output. I believe its because  I'm comparing a column and a mesure like IF(Pending = 'measue SUMX('CL', 'CL'[QTY BOXS])')

Well, i dont know if i was clear but if anyone could help me, i'd appreciate

1 ACCEPTED SOLUTION
Community Support

You can try these methods.

Solution 1:

Create a Measure as the following.

``````Is OK_ =

VAR inter_ =

FILTER (

'Base CL-beta',

'Base CL-beta'[Qty Boxs] IN VALUES ( 'SAP -beta'[Pendin] )

)

VAR val =

COUNTX ( inter_, [Qty Boxs] )

RETURN

IF ( ISBLANK ( val ), "nok", "ok" )``````

The result looks like this:

Solution 2:

1 Change the ‘cross filter direction’ from single to both between Base CL and PL

2 Create a Measure in Base CL

``Is Ok = IF(SELECTEDVALUE('Base CL'[Qty Boxs])=SELECTEDVALUE(SAP[Pendin]),"ok","nok")``

The result looks like this:

For more details, you can refer the attached pbix file. If you still have questions or I misunderstand your needs, please don't hesitate to let me known.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

Community Support

You can try these methods.

Solution 1:

Create a Measure as the following.

``````Is OK_ =

VAR inter_ =

FILTER (

'Base CL-beta',

'Base CL-beta'[Qty Boxs] IN VALUES ( 'SAP -beta'[Pendin] )

)

VAR val =

COUNTX ( inter_, [Qty Boxs] )

RETURN

IF ( ISBLANK ( val ), "nok", "ok" )``````

The result looks like this:

Solution 2:

1 Change the ‘cross filter direction’ from single to both between Base CL and PL

2 Create a Measure in Base CL

``Is Ok = IF(SELECTEDVALUE('Base CL'[Qty Boxs])=SELECTEDVALUE(SAP[Pendin]),"ok","nok")``

The result looks like this:

For more details, you can refer the attached pbix file. If you still have questions or I misunderstand your needs, please don't hesitate to let me known.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.