CODEWITHSUNDEEP
excelexcel-formulapowerbi
Victor York
Victor York

Reputation: 1681

Convert Excel formula to Power BI

Im a new user in Power BI and im having a lot of trouble trying to convert my formulas in excel to work in Power BI.

My main issue is figuring out how to convert IF(AND(OR and several IF's. I have several formulas similar to this one so with the help of converting this one I could most likely do the rest based off this one.

Parameters would be a table in power bi.

E2, Q2, $B$2, etc. would be columns/fields in different tables in power bi. 

=IF(E2="";"";IF(AND(OR(E2="Critical";E2="Maximum";E2="Urgent");Q2*24>Parameters!$B$2);"NO";
                                IF(AND(OR(E2="Alta";E2="High");Q2*24>Parameters!$B$3);"NO";
                                  IF(AND(OR(E2="Media";E2="Medium");Q2*24>Parameters!$B$4);"NO";
                                    IF(AND(OR(E2="Baja";E2="Low");Q2*24>Parameters!$B$5);"NO";
                               "YES")))))

This formula would be in a new column inside one of the tables.

Upvotes: 0

Views: 2371

Answers (2)

Nuel
Nuel

Reputation: 1

=IF(AND(LEFT(L6,3)="CGK",LEFT(M6,3)="CGK"),"Intracity",IF(AND(LEFT(N6,3)="CTC",LEFT(L6,3)=LEFT(M6,3)),"Intracity",IF(AND(LEFT(L6,3)=LEFT(M6,3),LEFT(N6,3)<>"CTC"),"Intercity","Domestik")))

Upvotes: 0

BenderIO
BenderIO

Reputation: 412

You already did the work, it's very very similar, I've just added a new column and modify your formula like this:

Column = IF(Table1[E2]="","",
            IF(AND(OR(Table1[E2]="Critical",OR(Table1[E2]="Maximum",Table1[E2]="Urgent")),Table1[Q2]*24>Table1[B2]),"No",
                IF(AND(OR(Table1[E2]="Alta",Table1[E2]="High"),Table1[Q2]*24>Table1[B2]),"No",
                    IF(AND(OR(Table1[E2]="Media",Table1[E2]="Medium"),Table1[Q2]*24>Table1[B2]),"No",
                        IF(AND(OR(Table1[E2]="Baja",Table1[E2]="Low"),Table1[Q2]*24>Table1[B2]),"No","YES"
                        )
                    )
                )
            )
        )

Where Table1 is my table, E2,Q2 and B2 are my columns name. About the "Q2*24", because you look like to use always Q2 and not Q2,Q3,Q4,... you can use one VAR instead of a full column with always the same value, like this:

Column = 
VAR ReplaceQ2 = 10
RETURN 
IF(Table1[E2]="","",
            IF(AND(OR(Table1[E2]="Critical",OR(Table1[E2]="Maximum",Table1[E2]="Urgent")),ReplaceQ2*24>Table1[B2]),"No",
                IF(AND(OR(Table1[E2]="Alta",Table1[E2]="High"),ReplaceQ2*24>Table1[B2]),"No",
                    IF(AND(OR(Table1[E2]="Media",Table1[E2]="Medium"),ReplaceQ2*24>Table1[B2]),"No",
                        IF(AND(OR(Table1[E2]="Baja",Table1[E2]="Low"),ReplaceQ2*24>Table1[B2]),"No","YES"
                        )
                    )
                )
            )
        )

Hope this help

Upvotes: 1

Related Questions