Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have two tables: Order and Case and I would like the Case table to lookup the OrderStatus in the Order table. They are connected through OrderId. If all OrderId within the CaseId has status 2 or 3 the column should return 1 otherwise 2. Its important that all OrderId either has the status 2 or 3.
Ordetstatus: 2 or 3 is = invoiced
Solved! Go to Solution.
@Anonymous
Invoiced =
IF (
ISEMPTY (
EXCEPT (
SELECTCOLUMNS (
FILTER ( ALL ( Order ), Order[CaseID] = Case[CaseID] ),
"@OrderStatus", Order[OrderStatus]
),
{ 2, 3 }
)
),
0,
1
)
One-One and Both
Thanks!
@Anonymous
Apologies for the late response. Please try
Invoiced =
IF (
1
IN CALCULATETABLE (
VALUES ( Order[OrderStatus] ),
ALLEXCEPT ( Case, Case[CaseID] )
),
0,
1
)
or
Invoiced =
IF (
1
IN SELECTCOLUMNS (
FILTER ( ALL ( Order ), Order[CaseID] = Case[CaseID] ),
"@OrderStatus", Order[OrderStatus]
),
0,
1
)
Thanks again! I managed to get the OrderStatus to the Case table but I´m still struggling on the code. I want to check if all the orders (can be multiple) within the same case has OrderStatus either 2 or 3 then return 1, otherwise 0. All orders need to have either 2 or 3 as OrderStatus to return 1.
@Anonymous
Invoiced =
IF (
ISEMPTY (
EXCEPT (
SELECTCOLUMNS (
FILTER ( ALL ( Order ), Order[CaseID] = Case[CaseID] ),
"@OrderStatus", Order[OrderStatus]
),
{ 2, 3 }
)
),
0,
1
)
Maybe I´m being a little bit inexplicit about the task. But I´´ve merged everything into one table. So right now I want to check if all the orders (can be multiple) within the same case has either OrderStatus 2 or 3 then return 1, otherwise 0. All orders need to have either 2 or 3 as OrderStatus to return 1.
Thank you @Anonymous
I have posted two answers and yet you did not specifically indicate what is wrong or what results are you getting. Would you please advise what results are getting out of the 2nd solution? I guess you need to switch position of 1 and 2 inside the IF statement
Hi, sorry for the late respond.
Regarding the 2nd solution the code points towards both tables, but I merged them into one. It worked when I had two separate tables. Thank you! One thing I dont get regarding the code is this:
"@OrderStatus"
Can you explain what it does?
Thanks again
@Anonymous
This just the alias column name argument of the SELECTCOLUMNS function. The @ sign is used as a standard when referencing a temporarily created column to be easily identified
Hi @Anonymous
What is the direction and the cardinality of the relationship?
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
55 | |
28 | |
20 | |
15 |