topic hierarchy filter based on the tables that are with many to many relationship in DAX Commands and Tips

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

santoshhmsam50 — Thu, 27 Jun 2024 21:59:08 GMT

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

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

Anonymous — Fri, 28 Jun 2024 02:00:11 GMT

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.

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

santoshhmsam50 — Wed, 10 Jul 2024 11:52:45 GMT

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