Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Help - Criteria from multiple tables

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

     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

4 Replies

  • Aron_Moore's avatar
    Aron_Moore
    Solution Specialist
    7 years ago

    Calculate filter should work.

     

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

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

     

    Best Regards

    Maggie

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

       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