Forum Discussion

aspirationalbec's avatar
aspirationalbec
Frequent Visitor
2 years ago
Solved

Filling in hierarchy fields

Hello and thank you in advance for your help.   fields - [mch_code] and [obj_level].   Obj_levels: 1 - contract  2 - site 3 - group barracks 4 - barracks 5 - building  5 - infrastructure 6...
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

hackcrr's avatar
hackcrr
Icon for Memorable Member rankMemorable Member
2 years ago

aspirationalbec 

Could you please describe the output you expect for the sample file you provided so that I can judge whether it is achievable. I looked at your file and maybe you can try using the following DAX expression:

bs_04_bar1 = 
IF (
    '240805-112000'[obj_level] = "ROOM",
    LOOKUPVALUE(
        '240805-112000'[mch_code],
        '240805-112000'[obj_level], "BARRACKS",
        '240805-112000'[sup_mch_code], LOOKUPVALUE(
            '240805-112000'[sup_mch_code],
            '240805-112000'[obj_level], "BUILDING",
            '240805-112000'[mch_code], '240805-112000'[sup_mch_code]
        )
    ),
    IF (
        '240805-112000'[obj_level] = "BUILDING",
        LOOKUPVALUE(
            '240805-112000'[sup_mch_code],
            '240805-112000'[obj_level], "BARRACKS",
            '240805-112000'[mch_code], '240805-112000'[sup_mch_code]
        ),
        BLANK()
    )
)
bs_03_gpbar1 = 
IF (
    '240805-112000'[obj_level] = "ROOM",
    LOOKUPVALUE(
        '240805-112000'[mch_code],
        '240805-112000'[obj_level], "Group Barracks",
        '240805-112000'[sup_mch_code], LOOKUPVALUE(
            '240805-112000'[sup_mch_code],
            '240805-112000'[obj_level], "BARRACKS",
            '240805-112000'[mch_code], LOOKUPVALUE(
                '240805-112000'[sup_mch_code],
                '240805-112000'[obj_level], "BUILDING",
                '240805-112000'[mch_code], '240805-112000'[sup_mch_code]
            )
        )
    ),
    IF (
        '240805-112000'[obj_level] = "BUILDING" || '240805-112000'[obj_level] = "BARRACKS",
        LOOKUPVALUE(
            '240805-112000'[sup_mch_code],
            '240805-112000'[obj_level], "Group Barracks",
            '240805-112000'[mch_code], LOOKUPVALUE(
                '240805-112000'[sup_mch_code],
                '240805-112000'[obj_level], "BARRACKS",
                '240805-112000'[mch_code], '240805-112000'[sup_mch_code]
            )
        ),
        BLANK()
    )
)

 

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

 

 

aspirationalbec's avatar
aspirationalbec
Frequent Visitor
2 years ago

hackcrr thank you for the suggestion. LOOKUPVALUE won't work as I'm connecting DQ, unfortunately. I thought I had it sussed when I found that formula, but unfortunately not. 

 

What I'm trying to do is fill in the gaps - I need the upper hierarchies to be populated. 

 

Room > building > barrack > grp barrack > site

 

The object level determines whether the mch_code is for what hierarchy. The sup_mch_code is always for the next hierarchy up. I need to find 2 levels up so the room barrack can be found by searching for the room sup_mch_code in the building's mch_code and return the building's [sup_mch_code]

 

Something like (because lookupvalue doesn't work):

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")))))
 
GRPBAR, SITE & 23 are error checking, they don't mean anything
  • hackcrr's avatar
    hackcrr
    Icon for Memorable Member rankMemorable Member
    2 years ago

    aspirationalbec 

    If you are using the DQ schema, you will be very limited when creating custom columns or creating calculated columns with PowerQuery. Essentially, you are using the DQ schema and your data sits in your data source with only basic table structures etc. in Power BI. So you will encounter that the Calculated Column formula above does not work.

     

    Best Regards

    hackcrr

    If I have answered your question, please mark my reply as solution and kudos to this post, thank you!