Visualising child data and parent data from the same fact table in two separate table visuals

Hi SebV 

You can refer to the following solution.

1.In dim_areas create the following calculated column

PV_IDS = var a=MAXX(FILTER(Dim_Areas,CONTAINSSTRING([Area_type],0)),[Area_id])
RETURN IF(CONTAINSSTRING([Area_type],1),a,Dim_Areas[Related PV_id])

2.I removed the dim_duplicate table. and create a new table. There is no relationship with this table.

Table =
VAR a =
    SUMMARIZE (
        FILTER ( Dim_Areas, CONTAINSSTRING ( [Area_type], "0" ) ),
        [Area_name],
        [Area_id],
        [PV_IDS]
    )
VAR b =
    SUMMARIZE (
        FILTER ( Dim_Areas, CONTAINSSTRING ( [Area_type], "1" ) ),
        [Area_name],
        [Area_id],
        [PV_IDS]
    )
VAR c =
    SUMMARIZE (
        FILTER ( Dim_Areas, CONTAINSSTRING ( [Area_type], "2" ) ),
        [Area_name],
        [Area_id],
        [Related PV_id]
    )
VAR d =
    SUMMARIZE (
        FILTER ( Dim_Areas, CONTAINSSTRING ( [Area_type], "3" ) ),
        [Area_name],
        [Area_id],
        [Related MU_id]
    )
VAR e =
    SUMMARIZE (
        FILTER ( Dim_Areas, CONTAINSSTRING ( [Area_type], "4" ) ),
        [Area_name],
        [Area_id],
        [Related DI_id]
    )
RETURN
    UNION ( a, b, c, d, e )

And in the new table create a calculated column.

Path = var a=PATH('Table'[Area_id],'Table'[PV_IDS])
return SUBSTITUTE(a,[Area_id],"")

 3.Then create a calculated columns in dim_area table.

Pathdetail = LOOKUPVALUE('Table'[Path],'Table'[Area_id],[Area_id])

4.Then create the following measures.

Measure = IF(ISFILTERED(Dim_Areas[Related MU_name]),COUNTROWS(FILTER(Dim_Areas,CONTAINSSTRING([Pathdetail],MAX('Table'[Area_id])))),1)

Inhabitants_1 = CALCULATE(SUM(Fact_Inhabitants[Value]),'Fact_Inhabitants'[Indicator] = "Inhabitants",Fact_Inhabitants[Area_id] in VALUES('Table'[Area_id]),CROSSFILTER(Dim_Areas[Area_id],Fact_Inhabitants[Area_id],None))

Male inhabitants (%)_1 = CALCULATE(SUM(Fact_Inhabitants[Value]),'Fact_Inhabitants'[Indicator] = "Men (%)",Fact_Inhabitants[Area_id] in VALUES('Table'[Area_id]),CROSSFILTER(Dim_Areas[Area_id],Fact_Inhabitants[Area_id],None))

Then create a table visual, and put the following field to the visual.

And put the measure to the table visual filter.

Output

Best Regards!

Yolo Zhu

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