Filling in hierarchy fields

[aspirationalbec]

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 - room

Please note the two categories on level 5. 

My issue is thus:

Having no room at the building level is to be expected. 

I need to fill in the barracks for the rooms, based on the building. I will then need to work up a level and work out the group barracks by the building, I'm assuming, until I can build up a proper picture. 

I've tried LOOKUPVALUE but it's not possible as DQ. due to the size of the data set, I can't create a new table to link together, it has to be a column, whether in dax or m. 

the bs_xx_xx columns are set up like this:

07_room = if [obj_level]="Room" then [mch_code]

06_building = if [obj_level]="Room" then "" else if [obj_level]="building" then [mch_code] else if [obj_level]="barrack" then [sup_mch_code]

I need the barrack column to pick up the building's [mch_code] at the room level, if that makes sense.

[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

[aspirationalbec]

OK I've saved the pbix here: https://drive.google.com/file/d/15a5OXlZwprI75lntM4oKdVB8VkxzOnVN/view?usp=sharing Hope that helps

Oh and I've not set up a relationship in the test data, but the table is on the 'one' side of a many to one relationship via the mch_code

[hackcrr]

@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]

@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]

@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!

[mickey64]

Please try using a "matrix" instead of a "table".

Rows --> bs_07_room

Columns --> blank

Values --> bs_06_building, bs_05_intra, etc...

[aspirationalbec]

Hi @mickey64, thanks for the suggestion but using a matrix wouldn't solve the issue. I need to use the hierarchy in graphs and visuals all over the report. The table was just to check if the columns I had created had worked.

[Ritaf1983]

Hi @aspirationalbec 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly