Using Filters in DAX for Two different tables

[Anonymous]

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.

[m3tr01d]

@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]

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]

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:

Region	DRBU	Driver	Category	Brand Type	Brand	Methodology type	2018	Scope Change	2019	Scope Change2	2020	Proc inv	Savings	Scope Change3	2021
CENTRAL	Combustibles Insights	Discretionary	Combustibles	Global Brand	Dunhill	Analytics	£                         -	£     39,270.00	£     39,270.00	-£    39,270.00	£                         -			£     40,700.00	£     40,700.00
CENTRAL	Combustibles Insights	Discretionary	Combustibles	Global Brand	Dunhill	Other	£                         -	£                         -	£                         -	£                         -	£                         -			£                         -	£                         -
CENTRAL	Combustibles Insights	Discretionary	Combustibles	Global Brand	Dunhill	Qual	£                         -	£  285,840.00	£  285,840.00	-£ 106,490.00	£  179,350.00			£  279,700.00	£  459,050.00
CENTRAL	Combustibles Insights	Discretionary	Combustibles	Global Brand	Dunhill	Quant	£     38,700.68	£     95,351.32	£  134,052.00	-£ 134,052.00	£                         -			£                         -	£                         -
CENTRAL	Combustibles Insights	Discretionary	Combustibles	Global Brand	KENT	Analytics	£                         -	£     95,010.00	£     95,010.00	-£    95,010.00	£                         -			£                         -	£                         -
CENTRAL	Combustibles Insights	Discretionary	Combustibles	Global Brand	KENT	Other	£                         -	£                         -	£                         -	£                         -	£                         -			£                         -	£                         -
CENTRAL	Combustibles Insights	Discretionary	Combustibles	Global Brand	KENT	Qual	£  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: 

Name	Description	Category	Category - FH	Category level 2	Category level 3	Category level 4	Methodology type	Methodology Group	Year	Budget
5041	Lucky Strike Modern AF Link	Cigarettes	Combustibles	Combustibles	Cigarettes	Combustibles	Quant	Communication Testing	2018	20,024
5545	Star LE BPT	Cigarettes	Combustibles	Combustibles	Cigarettes	Combustibles	Quant	Product Testing	2018	11,394
5545	Star LE BPT	Cigarettes	Combustibles	Combustibles	Cigarettes	Combustibles	Quant	Product Testing	2018	10,192
6638	Pall Mall BOOST BPT	Cigarettes	Combustibles	Combustibles	Cigarettes	Combustibles	Quant	Product Testing	2018	12,064
6638	Pall Mall BOOST BPT	Cigarettes	Combustibles	Combustibles	Cigarettes	Combustibles	Quant	Product Testing	2018	22,467
6788	2017 4cast KS Circumference	Cigarettes	Combustibles	Combustibles	Cigarettes	Combustibles	Quant	NPI/Concept/Mix Testing	2018	39,640
6788	2017 4cast KS Circumference	Cigarettes	Combustibles	Combustibles	Cigarettes	Combustibles	Quant	NPI/Concept/Mix Testing	2018	61,825
6834	Track - 2018	MultiCategory / Corporate	Multi-Category			Multi-Category/Corporate	Quant	Segmentation & Tracking	2018	84,084
6834	Track - 2018	MultiCategory / Corporate	Multi-Category			Multi-Category/Corporate	Quant	Segmentation & Tracking	2018	42,183
7260	Incidencia	MultiCategory / Corporate	Multi-Category			Multi-Category/Corporate	Quant	Segmentation & Tracking	2018	49,347

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

Please suggest