Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

IF AND statement from one table to another table

 

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

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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 )
    )
    )
     
     
     
     

    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

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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 )
    )
    )
     
     
     
     

    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's avatar
      Saxon10
      Post Prodigy
      5 years ago

      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. 

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      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's avatar
        Jihwan_Kim
        Super User
        5 years ago

        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.

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      Saxon10
      Post Prodigy
      5 years ago

      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.