Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a two tables are data and report.
In data table the following columns are A,B,C and status.
In report table the following columns are A,B and C.
In report table, I am trying to find out each columns values (A,B,C) are grater than or less than based on the data table. If it’s yes then return the comments “KP1” or “KPI-1” and if not then return “X”.
In Excel I am applying the following formula from data table into report table. How can I apply the same logic in Power BI?
=IF(AND(A2<=DATA!$C$2,B2<=DATA!$B$2,C2<=DATA!$D$2),"KPI",IF(AND(A2<=DATA!$C$3,B2<=DATA!$B$3,C2<=DATA!$D$3),"KPI-1","X"))
Example- If I have 10 columns in date table then I need to do same thing (compare 10 columns if statements) it’s so very painful is there any alternative option in Power BI so I can use different function with same logic.
I am looking for new calculated column option.
Any advise on this please.
Data:
STATUS | B | A | C |
KPI | 420 | 600 | 440 |
KPI-1 | 640 | 600 | 480 |
Report
A | B | C | STATUS |
250 | 250 | 160 | KPI |
600 | 400 | 140 | KPI |
300 | 260 | 240 | KPI |
240 | 160 | 160 | KPI |
320 | 320 | 320 | KPI |
320 | 320 | 320 | KPI |
600 | 440 | 140 | KPI-1 |
400 | 300 | 140 | KPI |
400 | 300 | 140 | KPI |
600 | 400 | 285 | KPI |
600 | 400 | 285 | KPI |
400 | 300 | 150 | KPI |
280 | 230 | 170 | KPI |
320 | 320 | 320 | KPI |
320 | 320 | 320 | KPI |
600 | 400 | 140 | KPI |
Report
Data
Solved! Go to Solution.
Hi, @Saxon10
I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below for creating a new column.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Saxon10
I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below for creating a new column.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
In data table if I have a multiple row then if condition so painful so is there any alternative way to get the same output? Can you please assist me.
Hi, @Saxon10
Please share your sample pbix file's link here that has multiple rows in the DATA Table with describing how your desired outcome looks like.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you so much for your reply and can you please explain how it's work and behind the logic. It will help to understand function.
@Saxon10
Correct me if I am wrong,. in your Excel formula, The values in row one in the Report Table (250,250,160) are all less than their respective columns in Data Table on both KPI and KPI-1, but you show only KPI as the result where as KPI-1 is also true.
Please clarify.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for your reply. You are right and that's ture. I am taking which one is match first incidence. Is there any option in power bi how can I exact match (appropriate) respective columns in Data Table on both KPI and KPI-1 columns.
Can you please advise.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |