hierarchy filter based on the tables that are with many to many relationship

[santoshhmsam50]

I have a table with  a LIKE 

a	b	C	CUSTOMER
LA	BC	CA	20
LC	BA		50
LD	CC

And tabbel B as same format 

a	b	c	sale	Session
LC	BA		500	32443
LD	BC		111	343
LA		CA	22	34324
LD		CA	333Need Help	34434

I want to create the  visulization with the  filterwith Hierarchy on a, b,c  th Show and Tellat can cover both the table  , looks to be the data model and DAX support

@DA @dax @rajendra 

@Ritaf1983

Ritaf1983

[v-kongfanf-msft · Accepted solution]

Hi @santoshhmsam50 ,

You need to create the following relational columns in each table and then create hierarchical tables. Then apply the following formula to the metric table.

CombinedKey = [a] & "|" & [b] & "|" & [c]

HierarchyTable = DISTINCT(
    UNION(
        SELECTCOLUMNS('TableA', "a", [a], "b", [b], "c", [c]),
        SELECTCOLUMNS('TableB', "a", [a], "b", [b], "c", [c])
    )
)

SumCustomer = 
CALCULATE(
    SUM(TableA[CUSTOMER]),
    TREATAS(
        VALUES(HierarchyTable[CombinedKey]),
        TableA[CombinedKey]
    )
)

SumSales = 
CALCULATE(
    SUM(TableB[sale]),
    TREATAS(
        VALUES(HierarchyTable[CombinedKey]),
        TableB[CombinedKey]
    )
)

Best Regards,
Adamk Kong

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

[v-kongfanf-msft · Accepted solution]

Hi @santoshhmsam50 ,

You need to create the following relational columns in each table and then create hierarchical tables. Then apply the following formula to the metric table.

CombinedKey = [a] & "|" & [b] & "|" & [c]

HierarchyTable = DISTINCT(
    UNION(
        SELECTCOLUMNS('TableA', "a", [a], "b", [b], "c", [c]),
        SELECTCOLUMNS('TableB', "a", [a], "b", [b], "c", [c])
    )
)

SumCustomer = 
CALCULATE(
    SUM(TableA[CUSTOMER]),
    TREATAS(
        VALUES(HierarchyTable[CombinedKey]),
        TableA[CombinedKey]
    )
)

SumSales = 
CALCULATE(
    SUM(TableB[sale]),
    TREATAS(
        VALUES(HierarchyTable[CombinedKey]),
        TableB[CombinedKey]
    )
)

Best Regards,
Adamk Kong

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

[santoshhmsam50 · Accepted solution]

Thanks a lot for  solving and explaining it in such a simple way 🙂