Sum values based on match and Filter columns of different tables

[samoberoi]

Hi all,

I need some help on where i have three fact tables and one dimension table

I have to sum the values on column A from Table_comb if the column B values from same table Table_comb match with Column E from Table_Ass and it should limit total to only include values in dates greater than or equal to current month day 1 and in dates less than current month+1 (which will be the next month). 
Is it possible? Will be thankful for the help.

Regards,

Sam

[AnushaSri · Accepted solution]

Hi @samoberoi ,

It can be possible by creating an inactive relation between table_comb and table _Ass. and in your DAX measure for sum , use userelationship function to activate inactive relation between these two table.

Please accept this as a solution if it resolve your issue. Also please give me kudos.

Thanks in advance.

[samoberoi]

Hi AnushaSri,

Thanks for replying. There is a common column between Table_Comm & Table_Ass, but when i  try to create that relationship, that will be many-to-many. I don't understand why do i need to use Userelationship function between these two tables and how is it going to work? 
Can you please explain a bit further on this?
Thanks

[AnushaSri]

Sorry I thought it would create a circular reference when you create a relation between Table_comm and Table_Ass but it is not.

So as you are saying there is a many to many relation is happening, I would prefer to create a bridge table by that holds all distinct Column E values from table_Ass and now you can create a relation between Table_Ass to Bridge table in bi-Direction and Bridge table to Table_Comm so that Table_Ass Column E values will filter bridge table which will again filter Table_Comm.

Please let me know if this make sense.

[samoberoi]

Hi AnushaSri,

It looks like the use of Userelationship function worked to some extent after i created an inactive 1-2-1 relationship between the two tables. However, i now need to calculate it within a specific time period and change to days e.g. it should only calculate it for the month of June that means from 01/06/2024 till 30/06/2024 and convert to days. I tried to use the following DAX but doesn't seem to work. 

DAX = CALCULATE (

             SUM ( Table_Eng[CW]),

USERELATIONSHIP ( Table_Ass[Id], Table_Eng[Id]),

DATESBETWEEN(Dates[Date],TODAY(),EOMONTH(EDATE(TODAY(),1),0)/7.5))

Thanks for your help.

Regards,

Sam