Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Create calculated column (in table2)with Lookup Value corresponding to matching ID from another table(Table 1) based on a condition where output value2 ,is lookup value with highest priroity from Table1 i.e.
1. Pause
2. No Pause
3. Other
Table 1 | |
Id1 | Value1 |
2002-10/01/2023 | No Pause |
2002-10/01/2023 | Pause |
2002-10/01/2023 | Other |
2103-10/01/2023 | Pause |
2103-10/01/2023 | Pause |
2105-10/01/2023 | Other |
2105-11/01/2023 | No Pause |
2106-11/01/2023 | Other |
Table 2 | |
Id2 | Value2 |
2002-10/01/2023 | Pause |
2103-10/01/2023 | Pause |
2105-10/01/2023 | No Pause |
2106-11/01/2023 | Other |
I treid creating calculated column as:
Value2 = LOOKUPVALUE(Table1[Value1],Table1[Merged Copy],Table2[ID2])
This gives error as mutiple value was supplied
Another way gives only first value
Value2 =
var xval= CALCULATE(FIRSTNONBLANK(Table1[Value1],TRUE()), FILTER(Table1,Table1[ID1]=Table2[ID2] ) )
return
if(xval="Pause" ,"Pause",IF(xval="No Pause","No Pause","Other"))
Solved! Go to Solution.
@Raj12 Maybe:
Column =
VAR __ID = [Id2]
VAR __Values = SELECTCOLUMNS( FILTER('Table 1', [Id1] = __ID), "__Value1", [Value1] )
VAR __Result =
SWITCH(TRUE(),
"Pause" IN __Values, "Pause",
"No Pause" IN __Values, "No Pause",
"Other" IN __Values, "Other",
BLANK()
)
RETURN
__Result
Hi @Raj12 ,
The formula @Greg_Deckler provided is worth looking at.
Also I created the following formula and was able to get the same results.
Value2 =
VAR CurrentID = Table2[Id2]
VAR HighestPriorityValue =
SELECTCOLUMNS (
FILTER (
Table1,
Table1[Id1] = CurrentID
&& ( Table1[Value1] = "Pause"
|| Table1[Value1] = "No Pause" )
),
"Value1", Table1[Value1]
)
RETURN
IF (
COUNTROWS ( HighestPriorityValue ) > 0,
MAXX ( HighestPriorityValue, [Value1] ),
"Other"
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Raj12 Maybe:
Column =
VAR __ID = [Id2]
VAR __Values = SELECTCOLUMNS( FILTER('Table 1', [Id1] = __ID), "__Value1", [Value1] )
VAR __Result =
SWITCH(TRUE(),
"Pause" IN __Values, "Pause",
"No Pause" IN __Values, "No Pause",
"Other" IN __Values, "Other",
BLANK()
)
RETURN
__Result
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |