Forum Discussion
DAX filter based on multiple table data
Hi ,
I have three tables.
1. DATA Table
Period Amount Budget
201901 200 A1
202001 3000 B1
201901 250 C1
2. Period Table:
Period Year Month
201901 2019 Jan
202001 2020 Jan
3. Budget Table
Budget Budget_Name
A1 Actual
B1 Budget
C1 Forecast
Datatable is linked to Period and Budget table.
Could you please help me in calculating a single measure that sums of Amount in data table based on below criteria:
(Year="2019" from period table and Budget="A1" from budget table) OR ( Year="2020" from period table and Budget="B1" from budget table)
Thanks in advance for your support.
6 Replies
- Anonymous Will try if there is a better approach than this. For now you try this
CALCULATE(SUM(tblData[Amount]),FILTER(tblPeriod,tblPeriod[Year]=2019),FILTER(tblBudget,tblBudget[Budget]="A1"))+CALCULATE(SUM(tblData[Amount]),FILTER(tblPeriod,tblPeriod[Year]=2020),FILTER(tblBudget,tblBudget[Budget]="B1")) Hi kpandey,
Please try the following measure:
MeasureSingle = CALCULATE(SUM('Data Table'[Amount]);FILTER('Period Table';'Period Table'[Year]=2020);FILTER('Budget Table';'Budget Table'[Budget]="B1"))+CALCULATE(SUM('Data Table'[Amount]);FILTER('Period Table';'Period Table'[Year]=2019);FILTER('Budget Table';'Budget Table'[Budget]="A1"))It returns 3200 according to data you provided, so that will do the job.If you need a measure that is more dynamic so that you can work with it on other data sets, please let me know.Thank you,Hi SaraMissBI,
Thanks for the DAX measure. This is measure is now working. However, as you mentioned below about more dynamic measure. I would like to hear about this. As I will have multiple conditions like below and summing up each will consume more time. Could you help me in preparing measures with multiple conditions? For example.
The table and data structure will be the same. Conditions are:
(Year="2019" from period table and Budget="A1" from budget table) OR ( Year="2020" from period table and Budget="B1" from budget table) OR (Year="2018" from period table and Budget="A1" from budget table) OR ( Year="2020" from period table and Budget="A1" from budget table)Thanks in advance for your support.
Hi Anonymous ,
I am glad my answer helped you. For me to help with making your measure more dynamic, I need to know what will control the conditions? is it for example a menu/slicer? If you are going to get the total for all possible cases then you won't need a measure just the amount will do the job.
Thank you,
