cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
henriquemalone
Helper II
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

OrderLineO+LQty BoxsReasonWeek
12345112345-112109516
12345212345-241109516
56789156789-110109516
54331154331-15109516
54331254331-21109516
54331354331-354109516
91845191845-132109516
12141112141-116109516

 

Table SAP:

OrderLineO+LDeliveryProductPendin
12345112345-11698238786769198712
12345112345-11691208056769198712
12345112345-11690156326769198712
12345212345-21691541356829058341
12345212345-21691541356827604641
56789156789-11697494946825989710
54331154331-1169679810678157905
54331154331-1169749580678157905

 

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: 

henriquemalone_0-1619701816196.png

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
v-cazheng-msft
Community Support
Community Support

Hi @henriquemalone 

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:

v-cazheng-msft_0-1620009596362.png

 

Solution 2:

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

v-cazheng-msft_1-1620009596367.png

 

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:

v-cazheng-msft_2-1620009596368.png

 

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!

 

View solution in original post

1 REPLY 1
v-cazheng-msft
Community Support
Community Support

Hi @henriquemalone 

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:

v-cazheng-msft_0-1620009596362.png

 

Solution 2:

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

v-cazheng-msft_1-1620009596367.png

 

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:

v-cazheng-msft_2-1620009596368.png

 

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!

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors