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

Hi, I like to show in addition to a table showing the number of inhabitants for selected districts or neighbourhoods a table with the number of inhabitants of the matching parent area: the municipal...

Need Help

Anonymous

2 years ago

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.