Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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:
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!
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:
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |