Filter Dimenion Table "twice"

[BB76]

Dear colleagues,

i am rather new to power bi and currently have a knot in my head.

I have a table which provides all company related information - see the following:

In my report if have filtered current company_id ("CO_BETIS_ID") ("selection"). Given this selection I would like to get the company name of the parent company. The above table provides the parent identifer as well ("CO_Parent_BETIS_ID") which is as well line item in this table. 

I think i need to filter the above table twice (in a simplified SQL-ish logic):

1: "parent_ID" =  Select CO_PARENT_BETIS_ID IN [TABLE] WHERE CO_BETIS_ID = "selection"

2: "parent_co_name" = Select CO_NAME IN [TABLE] WHERE  CO_BETIS_ID = "parent_ID"

#1 is working with the following DAX var declaration:

EITHER => var parentBetisID = CALCULATE(MAX(META_DATA[CO_PARENT_BETIS_ID]), FILTER(META_DATA, [CO_BETIS_ID]=SELECTEDVALUE(META_DATA[CO_BETIS_ID])))

OR => var parentBetisID = Maxx(META_DATA,META_DATA[CO_PARENT_BETIS_ID])

I don't know how to figure out #2 of the to-be-implemented measure.

Can someone please enlighten me?

Many thanks

BB

[v-kkf-msft · Accepted solution]

Hi @BB76 ,

Please try the measure.

ParentName = 
CALCULATE (
    MAX ( 'Table'[CO_NAME] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[CO_BETIS_ID] = MAX ( 'Table'[CO_PARENT_BETIS_ID] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BB76]

All thank you very much for your support here. Highly appreciated.

As a summary - following dax metric helped:

ParentCoName = 

var parentBetisID = CALCULATE(MAX(META_DATA[CO_PARENT_BETIS_ID]), FILTER(META_DATA,[CO_BETIS_ID]=SELECTEDVALUE(META_DATA[CO_BETIS_ID])))
var parentName = CALCULATE (
        MAX ( META_DATA[CO_ID_NAME] ),
        FILTER (
            ALL ( META_DATA ),
            META_DATA[CO_BETIS_ID] = parentBetisID 
        )
    )


return parentName

[amitchandak]

@BB76 , Table can not take slicer values.

You can have a measure like

var _tab = summarize(filter(allselected(Table), [CO_BETIS_ID] =SELECTEDVALUE(META_DATA[CO_BETIS_ID])), [CO_PARENT_BETIS_ID])
return
Countrows( Filter( Table, CO_BETIS_ID in _tab))

[BB76]

Hello amitchandak and thanks for your swiftly reply. 

This does not really work.

One example what i am heading for

CO_BETIS_ID	CO_NAME	CO_PARENT_BETIS_ID
1000	XYZ Ltd.	9999
9999	Parent Co Ltd.	0

Selection is: 1000

I want to get "Parent Co Ltd." as a result

DAX

"var _tab = summarize(filter(allselected(Table), [CO_BETIS_ID] =SELECTEDVALUE(META_DATA[CO_BETIS_ID])), [CO_PARENT_BETIS_ID])"

results correctly in "9999".

Question is how to get the wished result "Parent Co Ltd."

Many thanks thanks

BB