Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

Thanks in advance for your support.

6 REPLIES 6
saraMissBI
Resolver I
Resolver I

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

 

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,

 

Anonymous
Not applicable

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors