IF AND statement from one table to another table

[Saxon10]

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

[Jihwan_Kim · Accepted 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.

Status CC =

VAR conditions =

MINX (

FILTER (

Data,

Report[A] <= Data[A]

&& Report[B] <= Data[B]

&& Report[C] <= Data[C]

),

Data[A] + Data[B] + Data[C]

)

RETURN

IF (

CALCULATE (

MAX ( Data[STATUS] ),

FILTER ( data, Data[A] + Data[B] + Data[C] = conditions )

)

= BLANK (),

"X",

CALCULATE (

MAX ( Data[STATUS] ),

FILTER ( data, Data[A] + Data[B] + Data[C] = conditions )

)

)

https://www.dropbox.com/s/vvopg0di4i32ybg/saxonv3.pbix?dl=0 

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

[Saxon10]

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.

[Jihwan_Kim]

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.

[Saxon10]

@Jihwan_Kim ,

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. 

[Fowmy]

@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.

[Saxon10]

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.