Optimization of my DAX code

Question

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 ...&nbsp;Can you help me to optimize it please ?&nbsp;&nbsp;&nbsp;EVALUATE&nbsp;&nbsp;&nbsp;&nbsp;VAR u = UNION (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE ( Computer , computer[computer.cn]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE ( DevicesWithInventory , DevicesWithInventory[DeviceName2])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;u,computer[computer.cn],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"ClientType",IF(LEN(LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK()))=0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE(DevicesWithInventory[PCStatus],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK())),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"operatingSystem",IF(LEN(LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK()))=0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE(DevicesWithInventory[OperatingSystem],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK()))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)

dgpbi · Answer

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

Forum Discussion

Optimization of my DAX code

1 Reply

Related content

Dax code optimization

DAX performance optimization

DAX code issue

DAX query with SUMMARIZECOLUMNS - Optimization

Optimize Dax Code