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
Akbarov
Helper I
Helper I

How to sumifs with DAX?

Hello community,

 

I have tables which I imported to Power BI. ( Table1 , Table3 and Output table )

 

In output table, I want to create a new column and use DAX functions as SumIf

SUM Table3[Active]

IF

Table3[Client]=Output[Client],

Table3[Policy Year]= Output[Policy Year],

Table3[Month]<Output[Month],

Table3[Class]=Output[Class],

Table3[Gender]=Output[Gender]

 

But.. Output table is not related to Table3.

 

 

Output_data.png

 

2 ACCEPTED SOLUTIONS
SpartaBI
Community Champion
Community Champion

@Akbarov 

 

CC = 
VAR _client = Output[Client]
VAR _year = Output[Policy Year]
VAR _month = Output[Month]
VAR _class = Output[Class]
VAR _gender = Output[Class]
RETURN
SUMX(
	FILTER(
		Table3,
		Table3[Client]= _client
			&& Table3[Policy Year]= _year
				&& Table3[Month]< _month
					&& Table3[Class]= _class
						&& Table3[Gender]= _gender
	),
	Table3[Active]
)

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

SpartaBI
Community Champion
Community Champion

@Akbarov 

CC = 
VAR _client = Output1[Client]
VAR _year = Output1[Policy Year]
VAR _month = Output1[Month]
VAR _class = Output1[Class]
VAR _gender = Output1[Gender]
VAR _result = 
SUMX(
	FILTER(
		'(Table 3) Census',
		'(Table 3) Census'[Client] = _client
			&& '(Table 3) Census'[Policy Year]= _year
				&& '(Table 3) Census'[Month] < _month
					&& '(Table 3) Census'[Class]= _class
						&& '(Table 3) Census'[Gender]= _gender
	),
	'(Table 3) Census'[Active]
)

RETURN
    _result

View solution in original post

9 REPLIES 9
Akbarov
Helper I
Helper I

Any help please?

SpartaBI
Community Champion
Community Champion

@Akbarov hey friend, do you want to show me on zoom?

https://alqoritm.com/DECO/SHARE/SpartaBI.pbix

Here is the file. if need I can send you zoom link also.

SpartaBI
Community Champion
Community Champion

@Akbarov Hey! yep, can you share a link, I will come now

SpartaBI
Community Champion
Community Champion

@Akbarov 

CC = 
VAR _client = Output1[Client]
VAR _year = Output1[Policy Year]
VAR _month = Output1[Month]
VAR _class = Output1[Class]
VAR _gender = Output1[Gender]
VAR _result = 
SUMX(
	FILTER(
		'(Table 3) Census',
		'(Table 3) Census'[Client] = _client
			&& '(Table 3) Census'[Policy Year]= _year
				&& '(Table 3) Census'[Month] < _month
					&& '(Table 3) Census'[Class]= _class
						&& '(Table 3) Census'[Gender]= _gender
	),
	'(Table 3) Census'[Active]
)

RETURN
    _result

Would be great! or if you want I can share .pbix with you.

SpartaBI
Community Champion
Community Champion

@Akbarov 

 

CC = 
VAR _client = Output[Client]
VAR _year = Output[Policy Year]
VAR _month = Output[Month]
VAR _class = Output[Class]
VAR _gender = Output[Class]
RETURN
SUMX(
	FILTER(
		Table3,
		Table3[Client]= _client
			&& Table3[Policy Year]= _year
				&& Table3[Month]< _month
					&& Table3[Class]= _class
						&& Table3[Gender]= _gender
	),
	Table3[Active]
)

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Thank you very much for reply but I get error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

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