Forum Discussion
Multiple statement If result is match between two column then
Hi,
I have created two sample table as below :
I also attach here the formula for column STATUS for your reference:
| Statement 1: | IF tbl_kpi_submission[achievement] EQUAL 0 Then Result is Not Started |
| Statement 2: | IF tbl_kpi_submission[achievement] LESS tbl_kpi_master[threshold] Then Result is Below Threshold |
| Statement 3: | IF tbl_kpi_submission[achievement] EQUAL tbl_kpi_master[threshold] and LESS tbl_kpi_master[min] Then Result is Threshold |
| Statement 4: | IF tbl_kpi_submission[achievement] EQUAL tbl_kpi_master[min] and LESS tbl_kpi_master[target] Then Result is Min |
| Statement 5: | IF tbl_kpi_submission[achievement] EQUAL tbl_kpi_master[target] or MORE Then Result is Meet Target |
I really need someone to assist me on this since I'm new in Power BI ... I tried to find the solution in YouTube and web but none is match with my needs.
Regards,
NickzNickz
Use this code for 2nd table. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSrJLMlJBTGMDIyMgFQghA0kDPVMgaSBniGIMlVVitWJVjKC6TBC02EG4oOVg0gzPXMDoIZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [kpisubmission_id = _t, kpi_title = _t, year = _t, quarter = _t, achievement = _t, score = _t, weightage_score = _t, #"achievement_%" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"kpisubmission_id", Int64.Type}, {"kpi_title", type text}, {"year", Int64.Type}, {"quarter", type text}, {"achievement", Int64.Type}, {"score", type number}, {"weightage_score", type number}, {"achievement_%", Percentage.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"kpi_title", "year"}, tbl_kpi_master, {"kpi_title", "year"}, "tbl_kpi_master", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Result", each if [achievement]=0 then "Not Started" else if [achievement]<tbl_kpi_master[threshold]{0} then "Below Threshold" else if [achievement]=tbl_kpi_master[threshold]{0} and [achievement]<tbl_kpi_master[min]{0} then "threshold" else if [achievement]=tbl_kpi_master[min]{0} and [achievement]< tbl_kpi_master[target]{0} then "Threshold" else if [achievement]>=tbl_kpi_master[target]{0} then "Meet Target" else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"tbl_kpi_master"}) in #"Removed Columns"
3 Replies
Use this code for 2nd table. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSrJLMlJBTGMDIyMgFQghA0kDPVMgaSBniGIMlVVitWJVjKC6TBC02EG4oOVg0gzPXMDoIZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [kpisubmission_id = _t, kpi_title = _t, year = _t, quarter = _t, achievement = _t, score = _t, weightage_score = _t, #"achievement_%" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"kpisubmission_id", Int64.Type}, {"kpi_title", type text}, {"year", Int64.Type}, {"quarter", type text}, {"achievement", Int64.Type}, {"score", type number}, {"weightage_score", type number}, {"achievement_%", Percentage.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"kpi_title", "year"}, tbl_kpi_master, {"kpi_title", "year"}, "tbl_kpi_master", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Result", each if [achievement]=0 then "Not Started" else if [achievement]<tbl_kpi_master[threshold]{0} then "Below Threshold" else if [achievement]=tbl_kpi_master[threshold]{0} and [achievement]<tbl_kpi_master[min]{0} then "threshold" else if [achievement]=tbl_kpi_master[min]{0} and [achievement]< tbl_kpi_master[target]{0} then "Threshold" else if [achievement]>=tbl_kpi_master[target]{0} then "Meet Target" else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"tbl_kpi_master"}) in #"Removed Columns"Dear Vijay_A_Verma ,
" If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)" --> Yes, my actual table has more columns.
Based on your explanation, I need to change the yellow line below... Correct me if wrong ...
How about blue and green line... Do I need to change it too...
Steps :
1) First I need go to Home > Get Data > Open blank query.
2) Click Advanced Editor.
3) Replace existing text with the script you provided with changes (Yellow line).
4) Close and apply.
Let me know if the steps above are wrong...
Get your data through Power Query and perform a Changed type step.
Now copy my code starting with Merged Queries till the end and paste into your code after Changed Type step. Put a comma after Changed type step.
No other change is needed.
