Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous 

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 @Anonymous 

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.