Nested lookupvalue

[aspirationalbec]

I have a table of assets that range from a room, a building, a barrack, group of barracks, site. 

There is one field [mch_code] that uniquely identifies that item for each and a [sup_mch_code] that identifies and links the next level up in the hierarchy. 

The table looks like this:

MCH_CODE	SUP_MCH_CODE	OBJ_LEVEL	OPERATIONAL_STATUS
OUR WILTON SITE	LAR GROUP	BARRACKS	IN_OPERATION
NV 0 080	AIRFIELD_CMP_WEST	BUILDING	SCRAPPED
BX B 017	BULFORD	BUILDING	IN_OPERATION
BX B 018	BULFORD	BUILDING	IN_OPERATION
BX B 019	BULFORD	BUILDING	IN_OPERATION
BX B 020	BULFORD	BUILDING	IN_OPERATION
BX B 021	BULFORD	BUILDING	IN_OPERATION
BX B 021-001	BX B 021	ROOM	IN_OPERATION
BX B 021-002	BX B 021	ROOM	IN_OPERATION
BX B 021-003	BX B 021	ROOM	IN_OPERATION

All levels of the hierarchy are in this table, the [obj_level] detemines what level the row is at.

I'm using DQ but am managing to use LOOKUPVALUE as a calculated column. 

I'm trying to link together a room to a barrack which is two levels.

The building can be found via using the room's [sup_mch_code] but I need to then use the building's [sup_mch_code] to find the barracks. 

At the moment, I have:

bs_mch_bar =

var _roomsup = IF(EQUIPMENT__ID[OBJ_LEVEL]="ROOM",EQUIPMENT__ID[SUP_MCH_CODE])

RETURN

IF(AND(EQUIPMENT__ID[OBJ_LEVEL]="ROOM",EQUIPMENT__ID[SUP_MCH_CODE]=EQUIPMENT__ID[bs_mch_building]),LOOKUPVALUE(EQUIPMENT__ID[SUP_MCH_CODE],EQUIPMENT__ID[MCH_CODE],_roomsup),

IF(OR(EQUIPMENT__ID[OBJ_LEVEL]="BUILDING",EQUIPMENT__ID[OBJ_LEVEL]="INFRASTRUCTURE"),EQUIPMENT__ID[SUP_MCH_CODE],

IF(EQUIPMENT__ID[OBJ_LEVEL]="BARRACKS",EQUIPMENT__ID[MCH_CODE],

IF(EQUIPMENT__ID[OBJ_LEVEL]="GROUP OF BARRACKS","GRPBAR",

IF(EQUIPMENT__ID[OBJ_LEVEL]="SITE","SITE",

"23")))))

But now I'm getting an error message saying that LOOKUPVALUE isn't applicable in DQ. What is an alternative formula that I can use please? BTW, the GRPBAR, SITE & 23 fields are just there to error check!

[Greg_Deckler]

@aspirationalbec Can you post the data as text so it can be copy and pasted? You can always use MAXX or MINX instead of LOOKUPVALUE.

[aspirationalbec]

Hi @Greg_Deckler , edited the original post but here you are: 4

MCH_CODE	SUP_MCH_CODE	OBJ_LEVEL	OPERATIONAL_STATUS
OUR WILTON SITE	LAR GROUP	BARRACKS	IN_OPERATION
NV 0 080	AIRFIELD_CMP_WEST	BUILDING	SCRAPPED
BX B 017	BULFORD	BUILDING	IN_OPERATION
BX B 018	BULFORD	BUILDING	IN_OPERATION
BX B 019	BULFORD	BUILDING	IN_OPERATION
BX B 020	BULFORD	BUILDING	IN_OPERATION
BX B 021	BULFORD	BUILDING	IN_OPERATION
BX B 021-001	BX B 021	ROOM	IN_OPERATION
BX B 021-002	BX B 021	ROOM	IN_OPERATION
BX B 021-003	BX B 021	ROOM	IN_OPERATION

[aspirationalbec · Accepted solution]

Managed to work it out using a bit of SQL at source.

Posting here in case it helps anyone else:

select
eo.mch_code code,
eo.operational_status code_operational_status,

eo1.mch_code room,
eo1.operational_status room_operational_status,

eo2.mch_code building,
eo2.operational_status building_operational_status,

eo3.mch_code barracks,
eo3.operational_status barracks_operational_status,

eo4.mch_code grpbar,
eo4.operational_status grpbar_operational_status,

eo5.mch_code site,
eo5.operational_status site_operational_status from equipment_object eo

join equipment_object eo1 on eo.SUP_MCH_CODE = eo1.mch_code
join equipment_object eo2 on eo1.SUP_MCH_CODE = eo2.mch_code
join equipment_object eo3 on eo2.SUP_mch_code = eo3.MCH_CODE
join equipment_object eo4 on eo3.SUP_mch_code = eo4.MCH_CODE
join equipment_object eo5 on eo4.SUP_mch_code = eo5.MCH_CODE

[Anonymous]

Hi @aspirationalbec ,

Base on your description, it seems like it return syntax error when creating calculated column uner Direct Query connection mode. There are some limitations when using DirectQuery:

Modeling limitations

• Limitations in calculated columns: Calculated columns can only be intra-row, that is they can refer only to values of other columns of the same table, without using any aggregate functions. Also, the allowed DAX scalar functions, such as LEFT(), are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX query for a calculated column, and result in an error if used.

---

You can create a measure as below to get it:

bs_mch_bar =
VAR _level =
    SELECTEDVALUE ( EQUIPMENT__ID[OBJ_LEVEL] )
VAR _smcode =
    SELECTEDVALUE ( EQUIPMENT__ID[SUP_MCH_CODE] )
VAR _mcode =
    SELECTEDVALUE ( EQUIPMENT__ID[MCH_CODE] )
VAR _bsmbuild =
    SELECTEDVALUE ( EQUIPMENT__ID[bs_mch_building] )
RETURN
    SWITCH (
        TRUE (),
        _level = "ROOM"
            && _smcode = _mcode
            && _smcode = _bsmbuild, _smcode,
        _level IN { "BUILDING", "INFRASTRUCTURE" }, _smcode,
        _level = "BARRACKS", _mcode,
        _level = "GROUP OF BARRACKS", "GRPBAR",
        _level = "SITE", "SITE",
        "23"
    )

Best Regards