Re: Merge the tables with different number of colu...

binayjethwa · ‎03-13-2024

Hi ,

I am facing below issue in merging tables and comparing columns in different tables,

I have 3 tables emp_count , Emp_billable_1, Emp_billable_2 , i need create a final table from emp_count , emp_billable,emp_billable 1 by comparing billable , non billable and absence from both tables.

now billable and non billable from emp_billable table are created using certain formula in power bi ,i tried to merge all theree tables and write a measure to compare all the columns and give output , but since those columns are created from power bi , i cannot get them in power query , if i use lookupvalue and bring columns to single table i am seeing a table with multiple values error. and if i do relationship and use merasure i am seeing visual has exceeded available resources.

Emp_count

Name	Number	region
Ram	123	india
shyam	233	srilanka
naveen	344	thailand

emp_billable

Name	Number	Billable	Non Billable
Ram	123	44	1
shyam	233	32	13
naveen	344	33	12

emp_billable1

Name	Number	Billable	Non billable	absence
Ram	123	36		9
shyam	233	27	18
naveen	344	44	1

this is my sample dax i used in my measure. is there any waty to optimize the dax or any other way to get the result out please let me know.

Above tables are for sample purpose only.

To Action_Testing =

VAR DST_Billable = SELECTEDVALUE('DST'[DST_Billable])

VAR DST_NonBillable = SELECTEDVALUE(DST[DST_Non Billable])

VAR MERA_billable = SELECTEDVALUE('MERA'[Billable])

VAR MERA_Nonbillable = SELECTEDVALUE('MERA'[Non Billable])

VAR MERA_absence = SELECTEDVALUE('MERA'[Absence])

VAR region= SELECTEDVALUE(DST[Region])

return

SWITCH(

TRUE(),

dst_billable = 45 && mera_billable = 45 && (MERA_Nonbillable =45 || MERA_absence=45),"Double Booking" , --Logic 1

dst_billable = BLANK() && (MERA_billable<>BLANK()|| MERA_Nonbillable<>BLANK()),

"Remove Schedule from Current Week", ---Logic 2

dst_billable <>BLANK() && mera_billable <> BLANK() && MERA_Nonbillable = BLANK() && (MERA_absence = BLANK()||MERA_absence<>BLANK()),"Move hours from Billable code to Non Billable code", --- Logic 3

dst_billable = MERA_billable && MERA_Nonbillable = BLANK() && MERA_absence = BLANK(),"No Action",-- Logic 4

dst_billable =MERA_absence && MERA_Nonbillable = BLANK() && MERA_billable=BLANK() ,"No Action",--- Logic 5

dst_billable = BLANK() && MERA_billable =BLANK() && MERA_Nonbillable = BLANK() && MERA_absence = BLANK(),"No Action",

-- Logic 6

dst_billable = MERA_billable + MERA_Nonbillable + MERA_absence ,"No Action",--Logic 7

DST_NonBillable = MERA_Nonbillable && MERA_billable=BLANK() && (MERA_absence=BLANK() || MERA_absence<>BLANK()) ,"No Action",--Logic 8

dst_billable = (MERA_billable + MERA_Nonbillable) && MERA_absence= BLANK(),"No Action",--Logic 9

dst_billable = MERA_billable || DST_Billable= MERA_absence || DST_NonBillable =MERA_Nonbillable ||DST_NonBillable=MERA_absence ,"No Action",--Logic 10

dst_billable <> BLANK() &&

(MERA_billable>DST_Billable && MERA_Nonbillable>DST_Billable && MERA_absence>DST_Billable),"Reduce schedule from current week",--Logic 11

MERA_billable > DST_Billable && ( MERA_absence =BLANK() ||MERA_Nonbillable =BLANK()),"Reduce schedule from current week",--Logic 12

DST_NonBillable <> BLANK() && (MERA_billable =BLANK() || MERA_billable <> BLANK()) && MERA_Nonbillable >DST_NonBillable &&

(MERA_absence =BLANK() || DST_NonBillable=MERA_absence) ,"Reduce schedule from current week",---Logic 13

DST_NonBillable > MERA_Nonbillable && MERA_billable= BLANK() && MERA_absence=BLANK(),"Increase schedule hours for current week",--- Logic 14

dst_billable > MERA_billable && MERA_Nonbillable= BLANK()&& MERA_absence=BLANK() ,"Increase schedule hours for current week",--- Logic 15

dst_billable > MERA_billable && DST_NonBillable>MERA_Nonbillable && MERA_absence=BLANK() ,"Increase schedule hours for current week",--- Logic 16

dst_billable =BLANK() && MERA_billable <> BLANK()

&& MERA_Nonbillable= BLANK() &&(MERA_absence=BLANK() || MERA_absence<>BLANK()) , "Move hours from Billable code to Non Billable code",--logic 17

dst_billable <> BLANK() && MERA_billable = BLANK() && MERA_Nonbillable= BLANK() && MERA_absence<DST_Billable , "Update Retain with Schedules",--logic 18

DST_NonBillable <> BLANK() && MERA_billable = BLANK()&& MERA_Nonbillable= BLANK() && MERA_absence<DST_NonBillable , "Update Retain with Schedules",--logic 19

DST_Billable <> BLANK() && MERA_billable = BLANK() && MERA_Nonbillable<> BLANK() && (MERA_absence <> BLANK() ||MERA_absence=BLANK()), "Move Hours from Non Billable code to Billable")--logic 20

// dst_billable > MERA_billable && MERA_Nonbillable<> BLANK() && (MERA_absence <> BLANK() || MERA_absence=BLANK()), "Move Hours from Non Billable code to Billable"--logic 21

Anonymous · ‎03-14-2024

Hi @binayjethwa ,

There are a few things you could try to improve the performance of your measure:

Simplify the SWITCH statement by reducing the number of conditions and actions.
Use variables to store repeated expressions.
Use the IF statement instead of the SWITCH statement.

Please update the formula of your measure as below and check if it can work:

To Action_Testing =
VAR DST_Billable =
    SELECTEDVALUE ( 'DST'[DST_Billable] )
VAR DST_NonBillable =
    SELECTEDVALUE ( DST[DST_Non Billable] )
VAR MERA_billable =
    SELECTEDVALUE ( 'MERA'[Billable] )
VAR MERA_Nonbillable =
    SELECTEDVALUE ( 'MERA'[Non Billable] )
VAR MERA_absence =
    SELECTEDVALUE ( 'MERA'[Absence] )
VAR region =
    SELECTEDVALUE ( DST[Region] )
VAR Action =
    IF (
        dst_billable = 45
            && mera_billable = 45
            && ( MERA_Nonbillable = 45
            || MERA_absence = 45 ),
        "Double Booking",
        IF (
            dst_billable = BLANK ()
                && (
                    MERA_billable <> BLANK ()
                        || MERA_Nonbillable <> BLANK ()
                ),
            "Remove Schedule from Current Week",
            IF (
                dst_billable <> BLANK ()
                    && mera_billable <> BLANK ()
                    && MERA_Nonbillable = BLANK ()
                    && (
                        MERA_absence = BLANK ()
                            || MERA_absence <> BLANK ()
                    ),
                "Move hours from Billable code to Non Billable code",
                IF (
                    dst_billable = MERA_billable
                        && MERA_Nonbillable = BLANK ()
                        && MERA_absence = BLANK (),
                    "No Action",
                    IF (
                        dst_billable = MERA_absence
                            && MERA_Nonbillable = BLANK ()
                            && MERA_billable = BLANK (),
                        "No Action",
                        IF (
                            dst_billable = BLANK ()
                                && MERA_billable = BLANK ()
                                && MERA_Nonbillable = BLANK ()
                                && MERA_absence = BLANK (),
                            "No Action",
                            IF (
                                dst_billable = MERA_billable + MERA_Nonbillable + MERA_absence,
                                "No Action",
                                IF (
                                    DST_NonBillable = MERA_Nonbillable
                                        && MERA_billable = BLANK ()
                                        && (
                                            MERA_absence = BLANK ()
                                                || MERA_absence <> BLANK ()
                                        ),
                                    "No Action",
                                    IF (
                                        dst_billable = MERA_billable + MERA_Nonbillable
                                            && MERA_absence = BLANK (),
                                        "No Action",
                                        IF (
                                            dst_billable = MERA_billable
                                                || DST_Billable = MERA_absence
                                                || DST_NonBillable = MERA_Nonbillable
                                                || DST_NonBillable = MERA_absence,
                                            "No Action",
                                            IF (
                                                dst_billable <> BLANK ()
                                                    && ( MERA_billable > DST_Billable
                                                    && MERA_Nonbillable > DST_Billable
                                                    && MERA_absence > DST_Billable ),
                                                "Reduce schedule from current week",
                                                IF (
                                                    MERA_billable > DST_Billable
                                                        && (
                                                            MERA_absence = BLANK ()
                                                                || MERA_Nonbillable = BLANK ()
                                                        ),
                                                    "Reduce schedule from current week",
                                                    IF (
                                                        DST_NonBillable <> BLANK ()
                                                            && (
                                                                MERA_billable = BLANK ()
                                                                    || MERA_billable <> BLANK ()
                                                            )
                                                            && MERA_Nonbillable > DST_NonBillable
                                                            && (
                                                                MERA_absence = BLANK ()
                                                                    || DST_NonBillable = MERA_absence
                                                            ),
                                                        "Reduce schedule from current week",
                                                        IF (
                                                            DST_NonBillable > MERA_Nonbillable
                                                                && MERA_billable = BLANK ()
                                                                && MERA_absence = BLANK (),
                                                            "Increase schedule hours for current week",
                                                            IF (
                                                                dst_billable > MERA_billable
                                                                    && MERA_Nonbillable = BLANK ()
                                                                    && MERA_absence = BLANK (),
                                                                "Increase schedule hours for current week",
                                                                IF (
                                                                    dst_billable > MERA_billable
                                                                        && DST_NonBillable > MERA_Nonbillable
                                                                        && MERA_absence = BLANK (),
                                                                    "Increase schedule hours for current week",
                                                                    IF (
                                                                        dst_billable = BLANK ()
                                                                            && MERA_billable <> BLANK ()
                                                                            && MERA_Nonbillable = BLANK ()
                                                                            && (
                                                                                MERA_absence = BLANK ()
                                                                                    || MERA_absence <> BLANK ()
                                                                            ),
                                                                        "Move hours from Billable code to Non Billable code",
                                                                        IF (
                                                                            dst_billable <> BLANK ()
                                                                                && MERA_billable = BLANK ()
                                                                                && MERA_Nonbillable = BLANK ()
                                                                                && MERA_absence < DST_Billable,
                                                                            "Update Retain with Schedules",
                                                                            IF (
                                                                                DST_NonBillable <> BLANK ()
                                                                                    && MERA_billable = BLANK ()
                                                                                    && MERA_Nonbillable = BLANK ()
                                                                                    && MERA_absence < DST_NonBillable,
                                                                                "Update Retain with Schedules",
                                                                                IF (
                                                                                    DST_Billable <> BLANK ()
                                                                                        && MERA_billable = BLANK ()
                                                                                        && MERA_Nonbillable <> BLANK ()
                                                                                        && (
                                                                                            MERA_absence <> BLANK ()
                                                                                                || MERA_absence = BLANK ()
                                                                                        ),
                                                                                    "Move Hours from Non Billable code to Billable",
                                                                                    "No Action"
                                                                                )
                                                                            )
                                                                        )
                                                                    )
                                                                )
                                                            )
                                                        )
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
RETURN
    Action

In addtion, you can refer the following links to optimize your DAX formulas:

For DAX:

DAX Best Practice Guide

Optimizing DAX Video Course

Best Regards