Forum Discussion

aspirationalbec's avatar
aspirationalbec
Frequent Visitor
2 years ago
Solved

Nested lookupvalue

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] th...
Need Help
  • aspirationalbec's avatar
    aspirationalbec
    2 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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's avatar
aspirationalbec
Frequent Visitor
2 years ago

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

MCH_CODESUP_MCH_CODEOBJ_LEVELOPERATIONAL_STATUS
OUR WILTON SITELAR GROUPBARRACKSIN_OPERATION
NV 0 080AIRFIELD_CMP_WESTBUILDINGSCRAPPED
BX B 017BULFORDBUILDINGIN_OPERATION
BX B 018BULFORDBUILDINGIN_OPERATION
BX B 019BULFORDBUILDINGIN_OPERATION
BX B 020BULFORDBUILDINGIN_OPERATION
BX B 021BULFORDBUILDINGIN_OPERATION
BX B 021-001BX B 021ROOM IN_OPERATION
BX B 021-002BX B 021ROOM IN_OPERATION
BX B 021-003BX B 021ROOM IN_OPERATION
  • aspirationalbec's avatar
    aspirationalbec
    Frequent Visitor
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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