topic Re: DAX filter based on multiple table data in DAX Commands and Tips

DAX filter based on multiple table data

Anonymous — Thu, 24 Oct 2019 05:49:32 GMT

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.

Re: DAX filter based on multiple table data

Anonymous — Thu, 24 Oct 2019 08:32:04 GMT

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"))

Re: DAX filter based on multiple table data

saraMissBI — Thu, 24 Oct 2019 09:02:38 GMT

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,

Re: DAX filter based on multiple table data

Anonymous — Thu, 24 Oct 2019 10:59:59 GMT

Thank you very much.

This measure is working.

Re: DAX filter based on multiple table data

Anonymous — Thu, 24 Oct 2019 11:07:17 GMT

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.

Re: DAX filter based on multiple table data

saraMissBI — Sat, 26 Oct 2019 16:46:52 GMT

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,

Re: DAX filter based on multiple table data

Anonymous — Wed, 30 Oct 2019 13:00:47 GMT

Hi saraMissBI ,

Thanks again for your response.
I am looking for the option without using slicers for the possible conditions. However, as you mentioned below on the way to get total for all the possible cases without using measure. I would love to hear on this.
Also, on my data table the amount filed is YTD. Period filed is in the "yyyymm" format with data type text. Are there any ways to get the monthly figures from the YTD figures for my data?

Thanks in advance for your support.