DAX Help - Criteria from multiple tables

[Anonymous]

Hey All,

I have a situation where I need to create a measure that takes into account data from multiple tables.

The first table is a membership table. There is a row for each person and the month they are eligible. The second table is a transaction table.

I want to do a calculation with the following parameters

Sum number of transactions of a certain type from the transaction table where the transaction month = membership month. So if this is February 2019 and all your transactions of the specified type are prior February they would be excluded.

I'm thinking I need a calculate with filter but I am thinking the filter should say where membership month = transaction month but that isn't working. Any ideas?

Thanks!

[Ashish_Mathur]

Hi,

Create a relationship from the Data column in your transaction table to the date column in the Calenda Table.  In the Calendar Table, create two additional columns for Year and Month.  Year = YEAR(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").  In your visual, drag Year and Month from the Calendar Table.  Write this measure

Total sales = SUM(Data[Sales])

Hope this helps.

[Anonymous · Accepted solution]

 Hi all,

Thanks for all the help. After digging into this further I found that the issue is likely due to the relationship passing through an intermediate table which was confusing the DAX. I used SQL to bring the data over to one table and the calculation is no working properly.

James

[v-juanli-msft]

Hi @Anonymous 

I'm working on this, it is better to provide some example data which fit your scenario.

Best Regards

Maggie

[Aron_Moore]

Calculate filter should work.

Could you post a snip of data (fake if you need to) and the measure you tried?