Forum Discussion

DGPBi's avatar
DGPBi
Icon for Helper I rankHelper I
3 years ago

Optimization of my DAX code

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...
DGPBi's avatar
DGPBi
Icon for Helper I rankHelper I
3 years ago

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