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

Need Help

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

8 Replies

Ritaf1983
Super User
2 years ago
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
mickey64
Super User
2 years ago
Please try using a "matrix" instead of a "table".

Rows --> bs_07_room

Columns --> blank

Values --> bs_06_building, bs_05_intra, etc...
- aspirationalbec
  Frequent Visitor
  2 years ago
  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.
aspirationalbec
Frequent Visitor
2 years ago
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
  Memorable 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
    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
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

Forum Discussion

Filling in hierarchy fields

8 Replies

Related content

Field Parameter Hierarchy

Filled map

Date hierarchy

remove the date hierarchy for a single date field

Can date fields used as parameter fields have date hierarchy?