Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have two tables, where table A holds the primary unique IDs of each sales order and table B holds the information of each sales order:
Table A
Part Number | Sales Order |
AAA | 123 |
BBB | 456 |
CCC | 789 |
Table BL
Sales Order | Hold Description | Hold Date | Resolved |
789 | Bad Part | 1/1/2022 | Yes |
456 | |||
789 | Bad Paint | 1/2/2022 | Yes |
123 | Bad Part | 1/25/2022 | Yes |
123 | Bad Paint | No |
I'm trying to create a column on table A to show the issue of each sales order but as you see there's a one-to-many relationship and I'm trying to create a DAX column to show if the sales order is on hold or not based of on the criterias below:
1) If there's no "Hold Description" the returned results should be "No Issue"
2) If there's a "Hold Description" and no "Resolved" text, then the result should be "Has Issue"
so essentially the out put table A with added column would be:
Part Number | Sales Order | Current Issue |
AAA | 123 | Has Issue |
BBB | 456 | No Issue |
CCC | 789 | No Issue |
My current DAX formula is stating that Sales Order "456" as "Has Issue" because one of the looked at column criterias in the lookupvalue is true:
Current Issue Status =
IF(LOOKUPVALUE(TableB[Sales Order], TableB[Sales Order], TableA[Sales Order], TableB[Resolved], BLANK(), Blank()) = BLANK(), "No Issue", "Has Issue")
Is there a way to filter "Table B" first to get rid of all the rows that have no current "Hold Descriptions" and then peform a LookUpValue on that filtered table??
Thank you!!
Solved! Go to Solution.
Hi @OrionTL ,
Here are the steps you can follow:
1. Create calculated column.
Column =
VAR _selecthold =
SELECTCOLUMNS (
FILTER (
ALL ( TableB ),
'TableB'[Sales Order] = EARLIER ( 'TableB'[Sales Order] )
),
"1", [Hold Description]
)
VAR _selectresolve =
SELECTCOLUMNS (
FILTER (
ALL ( TableB ),
'TableB'[Sales Order] = EARLIER ( 'TableB'[Sales Order] )
),
"1", [Resolved]
)
RETURN
IF (
'TableB'[Hold Description]
IN _selecthold
&& 'TableB'[Resolved]
IN _selectresolve
&& 'TableB'[Resolved] = "No",
"Has Issue",
"No Issue"
)
Current Issue =
VAR _count =
COUNTX (
FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
[Column]
)
RETURN
IF (
'TableA'[Sales Order]
IN SELECTCOLUMNS ( 'TableB', "1", 'TableB'[Sales Order] )
&& "No"
IN SELECTCOLUMNS (
FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
"2", [Resolved]
),
MAXX (
FILTER (
ALL ( TableB ),
'TableB'[Sales Order] = 'TableA'[Sales Order]
&& 'TableB'[Resolved] = "No"
),
[Column]
),
MAXX (
FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
[Column]
)
)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @OrionTL
The following should work fine
Current Issue =
IF (
"No" IN CALCULATETABLE ( VALUES ( 'Table B'[Resolved] ) ),
"Has Issue",
" No Issue"
)
Hi @OrionTL ,
Here are the steps you can follow:
1. Create calculated column.
Column =
VAR _selecthold =
SELECTCOLUMNS (
FILTER (
ALL ( TableB ),
'TableB'[Sales Order] = EARLIER ( 'TableB'[Sales Order] )
),
"1", [Hold Description]
)
VAR _selectresolve =
SELECTCOLUMNS (
FILTER (
ALL ( TableB ),
'TableB'[Sales Order] = EARLIER ( 'TableB'[Sales Order] )
),
"1", [Resolved]
)
RETURN
IF (
'TableB'[Hold Description]
IN _selecthold
&& 'TableB'[Resolved]
IN _selectresolve
&& 'TableB'[Resolved] = "No",
"Has Issue",
"No Issue"
)
Current Issue =
VAR _count =
COUNTX (
FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
[Column]
)
RETURN
IF (
'TableA'[Sales Order]
IN SELECTCOLUMNS ( 'TableB', "1", 'TableB'[Sales Order] )
&& "No"
IN SELECTCOLUMNS (
FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
"2", [Resolved]
),
MAXX (
FILTER (
ALL ( TableB ),
'TableB'[Sales Order] = 'TableA'[Sales Order]
&& 'TableB'[Resolved] = "No"
),
[Column]
),
MAXX (
FILTER ( ALL ( TableB ), 'TableB'[Sales Order] = 'TableA'[Sales Order] ),
[Column]
)
)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |