Forum Discussion

robbielambert's avatar
robbielambert
Frequent Visitor
6 years ago

Complex table filter within a measure

Hi,   I have two tables identical in design to the examples in the image the below. Table 1 contains ~100K rows, table 2 contains ~500K rows.    I am looking to build measures which would allow m...
Pragati11's avatar
Pragati11
Super User
6 years ago

Hi robbielambert ,

 

Have you tried creating relationship between these 2 tables on "Customer" and then try performing the 3 steps?

Also, can you share the DAX or calculation you are using for the 3 steps you mentioned?

 

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

robbielambert's avatar
robbielambert
Frequent Visitor
6 years ago

Hi Pragati11,

 

Thanks for getting in touch. 

 

I have a join on customer between the two tables as you describe. It is then the step of defining which customers are in a specific category on a given date that is where i am failing.

 

The approach I have taken is to create a table variable which filters for the accounts in a specifc category on a specific date, and then try to filter the transactions table using the list I have created. However, I cannot seem to restrict the transactions table by the table variable I have created. I'm sure there must be an easier way!!

 

Here is my DAX:

 

Transactions Amount =

VAR VarTable = CALCULATETABLE('Categories','Categories'[From Date] <= EOMONTH(today(),-2), 'Categories'[To Date] > EOMONTH(today(),-2),'Categories'[category] = "A")

RETURN

CALCULATE(SUMX(CALCULATETABLE('Transactions',EXCEPT(VALUES('Transactions'[Customer]), VALUES(I want to reference VarTable here))),'Transactions'[Amount]))
 
Thanks