Forum Discussion
Create a new column based on multiple conditions
Hi Anonymous
1. Create them as 2 different tables. Let me call the first table as Table1.
2. For the second Table I would create it with columns Min%, Max%, MScore, Vscore
eg 0,50,0,0
51,75,500,25
76,90,1000,200
91,100,2000,300
3. Now we want to add the Vscore from table2 as score in table1 based on the condition.
4. Crate a calculated column Vscore in Table1 with the expression
CALCULATE (VALUES ( Table2[Vscore] ),FILTER (Table2,Table1[Actual/Target%] >= Table2[Min%]&& Table1[Actual/Target%] <= Table2[Max%]))This will get the Vscore against each row value of Table1[Actual/Target%]
Cheers
CheenuSing
Hi Anonymous
1. Create them as 2 different tables. Let me call the first table as Table1.
2. For the second Table I would create it with columns Min%, Max%, MScore, Vscore
eg 0,50,0,0
51,75,500,25
76,90,1000,200
91,100,2000,300
3. Now we want to add the Vscore from table2 as score in table1 based on the condition.
4. Crate a calculated column Vscore in Table1 with the expression
This will get the Vscore against each row value of Table1[Actual/Target%]
Cheers
CheenuSing
Hi Anonymous ,
It worked like a charm. I have futher developed your logic to make it work in my case. Thanks a lot for your time on this.
Calc Column =
VAR VScore =
CALCULATE (
VALUES ( Table2[Vscore] ),
FILTER (
Table2,
Table1[Actual/Target%] >= Table2[Min%]
&& Table1[Actual/Target%] <= Table2[Max%]
)
)
VAR MScore =
CALCULATE (
VALUES ( Table2[Mscore] ),
FILTER (
Table2,
Table1[Actual/Target%] >= Table2[Min%]
&& Table1[Actual/Target%] <= Table2[Max%]
)
)
RETURN
IF ( Table1[Type] = "V", VScore, MScore )
Cheers
Charan