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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Using Filters in DAX for Two different tables

How we can use two different data sets in measure, I had written below dax query but unable to get correct result :
Methodology - New Actives 2020 = VAR Budget =
CALCULATE(
SUM ('Raw Data'[Budget] ),
FILTER ('Raw Data', 'Raw Data'[Year]=2020),
FILTER ('Data (2)', 'Data (2)'[Category]="New Actives")
)/ 1000000
RETURN Budget
 
Could you please suggest.
3 REPLIES 3
m3tr01d
Continued Contributor
Continued Contributor

@Anonymous 
1) Make a table "Category" with only the unique Category in Power BI
2) Create relationships from Data to Category and RAW data to Category
3) New measure 

CALCULATE(
	SUM ('Raw Data'[Budget] ),
	'Raw Data'[Year]=2020,
	NewCategoryTable[Category]="New Actives"
)/ 1000000

 

Your biggest issue is you don't have a proper Data Model, you need to split your data in Fact tables and Dimensions then build relationships

VahidDM
Super User
Super User

Hi @Anonymous 

 

The second filter is incorrect; you need to add the "Category" column to the "Raw Data" table, then use that in the measure, or use the relation between those two tables.

Try to use Related dax code or share a sample of your data here in a table format. 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

 

Anonymous
Not applicable

Hi @VahidDM 

 

Thanks for prompt reply.

 

I have excel data where below formula is used:

 

=SUMIFS('Raw Data'!AB:AB,Data!D:D,$C$501,'Raw Data'!AA:AA,$K$501,'Raw Data'!N:N,J502)/1000000

 

Below is the sample data set: 

 

below table is Data:

 

RegionDRBUDriverCategoryBrand TypeBrandMethodology type2018Scope Change2019Scope Change22020

Proc

inv

SavingsScope Change32021
CENTRALCombustibles InsightsDiscretionaryCombustiblesGlobal BrandDunhillAnalytics £                         -   £     39,270.00 £     39,270.00-£    39,270.00 £                         -     £     40,700.00 £     40,700.00
CENTRALCombustibles InsightsDiscretionaryCombustiblesGlobal BrandDunhillOther £                         -   £                         -   £                         -   £                         -   £                         -     £                         -   £                         -  
CENTRALCombustibles InsightsDiscretionaryCombustiblesGlobal BrandDunhillQual £                         -   £  285,840.00 £  285,840.00-£ 106,490.00 £  179,350.00   £  279,700.00 £  459,050.00
CENTRALCombustibles InsightsDiscretionaryCombustiblesGlobal BrandDunhillQuant £     38,700.68 £     95,351.32 £  134,052.00-£ 134,052.00 £                         -     £                         -   £                         -  
CENTRALCombustibles InsightsDiscretionaryCombustiblesGlobal BrandKENTAnalytics £                         -   £     95,010.00 £     95,010.00-£    95,010.00 £                         -     £                         -   £                         -  
CENTRALCombustibles InsightsDiscretionaryCombustiblesGlobal BrandKENTOther £                         -   £                         -   £                         -   £                         -   £                         -     £                         -   £                         -  
CENTRALCombustibles InsightsDiscretionaryCombustiblesGlobal BrandKENTQual £  206,238.76 £  275,969.24 £  482,208.00-£    34,202.99 £  448,005.01  -£ 212,287.11 £  235,717.90

 

Below Table is Raw Data: 

 

NameDescriptionCategoryCategory - FHCategory level 2Category level 3Category level 4Methodology typeMethodology GroupYearBudget
5041Lucky Strike Modern AF LinkCigarettesCombustiblesCombustiblesCigarettesCombustiblesQuantCommunication Testing201820,024
5545Star LE BPTCigarettesCombustiblesCombustiblesCigarettesCombustiblesQuantProduct Testing201811,394
5545Star LE BPTCigarettesCombustiblesCombustiblesCigarettesCombustiblesQuantProduct Testing201810,192
6638Pall Mall BOOST BPTCigarettesCombustiblesCombustiblesCigarettesCombustiblesQuantProduct Testing201812,064
6638Pall Mall BOOST BPTCigarettesCombustiblesCombustiblesCigarettesCombustiblesQuantProduct Testing201822,467
67882017 4cast KS CircumferenceCigarettesCombustiblesCombustiblesCigarettesCombustiblesQuantNPI/Concept/Mix Testing201839,640
67882017 4cast KS CircumferenceCigarettesCombustiblesCombustiblesCigarettesCombustiblesQuantNPI/Concept/Mix Testing201861,825
6834Track - 2018MultiCategory / CorporateMulti-Category  Multi-Category/CorporateQuantSegmentation & Tracking201884,084
6834Track - 2018MultiCategory / CorporateMulti-Category  Multi-Category/CorporateQuantSegmentation & Tracking201842,183
7260IncidenciaMultiCategory / CorporateMulti-Category  Multi-Category/CorporateQuantSegmentation & Tracking201849,347

 

Category FH in Raw Data and Category in Data have same attributes.

 

Please suggest

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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