Optimization of my DAX code

DGPBi — Wed, 06 Sep 2023 15:06:18 GMT

Hello,

I'm trying to create a new table from 2 other tables without any relationship between them.

The goal is to add all distinct Computer name from the 2 tables and add some selected columns from them too.

After that I need to select some info from 1 column of 1 of the tables. Column names in tables can have the same column name or can be different.

My code works but I have more or less 15 other columns to add and I'm not sure my code is the best one ...

Can you help me to optimize it please ?

EVALUATE
    VAR u = UNION (
        SUMMARIZE ( Computer , computer[computer.cn]),
        SUMMARIZE ( DevicesWithInventory , DevicesWithInventory[DeviceName2])
        )
RETURN
    SUMMARIZE (
        u,computer[computer.cn],
        "ClientType",IF(LEN(LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK()))=0,
                                LOOKUPVALUE(DevicesWithInventory[PCStatus],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()),
                                LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK())),
        "operatingSystem",IF(LEN(LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK()))=0,
                                LOOKUPVALUE(DevicesWithInventory[OperatingSystem],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()),
                                LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK()))


    )

Re: Optimization of my DAX code

DGPBi — Thu, 07 Sep 2023 06:49:36 GMT

hello all,

This query seems a little bit more efficient:

EVALUATE VAR u = UNION ( SUMMARIZE ( computer , computer[computer.cn]), SUMMARIZE ( DevicesWithInventory , DevicesWithInventory[DeviceName2]) ) RETURN SUMMARIZE ( u,computer[computer.cn], "Device ID", VAR sComputer =LOOKUPVALUE(computer[Device ID],computer[computer.cn],[computer.cn],BLANK()) VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[Device ID],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()) RETURN SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer) , "ClientType", VAR sComputer =LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK()) VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[PCStatus],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()) RETURN SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer), "OperatingSystem", VAR sComputer = LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK()) VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[OperatingSystem],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()) RETURN SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer), "PCStatus", VAR sComputer =LOOKUPVALUE(computer[PCStatus],computer[computer.cn],[computer.cn],BLANK()) VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[Device state],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()) RETURN SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer), "User ID", /*VAR sComputer =LOOKUPVALUE(User[UserID],computer[computer.cn],[computer.cn],BLANK())*/ VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[UserID],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()) RETURN /*SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer)*/ sDeviceInventory

topic Re: Optimization of my DAX code in DAX Commands and Tips

Optimization of my DAX code

Re: Optimization of my DAX code