cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

6 REPLIES 6
Resolver I

Hi kpandey,

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,
Anonymous
Not applicable

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)

Resolver I

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,

Anonymous
Not applicable

Hi @saraMissBI ,

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?

Anonymous
Not applicable
@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"))
Anonymous
Not applicable

Thank you very much.

This measure is working.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.