cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BKnecht
Helper II
Helper II

DAX Calculate IF OR Statement

Im pretty new to writing DAX, and am trying to do something that would be simple in excel, but I can't seem to create it in Power BI. 

 

I'm trying to create a custom measure that says:

 

Calculate the Sum of Column X IF Column A = "Renewal" OR Column B = "Needs Alignment".

 

Very simple measure, but I can't seem to get the OR statement in there, and I can't use the filters in the Calculate function because once I put "Column X = "Renewal", it filters out all of the "Column B = "Needs Alignment". 

 

Any ideas? Any help would be greatly appreciated. 

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

CALCULATE(

SUM(Table[Column X]),

FILTER(

Table,

Table[Column A] = "Renewal" ||

Table[Column B] = "Needs Alignment")

)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

16 REPLIES 16
KHorseman
Community Champion
Community Champion

CALCULATE(

SUM(Table[Column X]),

FILTER(

Table,

Table[Column A] = "Renewal" ||

Table[Column B] = "Needs Alignment")

)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

Lets say both values "renewal" and "needs assignment" are in column A,
is there a way to mention both values in the filter (or anywhere else) without haviing the mention Table [column a] twice?

so instead of:

Table[Column A] = "Renewal" ||

Table[Column A] = "Needs Alignment"

 

Something like:

Table[Column A] = ("Renewal" || "Needs Alignment")

 

tried creating a VAR to return into my logical function but I dont get it right somehow

 

Any ideas? @KHorseman 

KHorseman
Community Champion
Community Champion

@Anonymous  Table[Column A] IN {"Renewal", "Needs Assignment"}





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




That was so much easier than I was trying to make it, didn't even know about the Filter function, this worked perfectly, Thank you!

KHorseman
Community Champion
Community Champion

CALCULATE(<expression>, FILTER( <table>, <table[column] = condition>)) is probably the most generally useful pattern to learn in DAX. The vast majority of measures I write either follow this pattern or contain a part that follows this pattern.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi KHorseman,

 

The CALCULATE/FILTER syntax you supplied really helped me with DAX statements in general.

 

Thanks,

Dan

Hi there

 

Thanks for this solution - it guided me a step further with a filter challange Smiley Happy

i'm currently struggling with adding multiple filters:

 

POS	Attribut	Value
1	A	10
2	B	200
3	C	3000
4	D	40000
5	E	500000
6	A	10
7	B	200
8	C	3000
9	D	40000
10	E	500000

 

 

I summed the total of all SAP[Value] where [Attribute] is "A":

Total_AB = CALCULATE(SUM(SAP[Value]);
	FILTER(
SAP;
SAP[Attribut] = "B")
)

 

But I need to Sum the total of all SAP[Value] where [Attribute] is "A", "B" and "E" - but the filter functions allow only 2 statements. Does someone has an idea how to solve this with DAX?

 

Thx! Patrick

KHorseman
Community Champion
Community Champion

@Willborn

 

Total_ABE = CALCULATE(
	SUM(SAP[Value]);
	FILTER(
		SAP;
		SAP[Attribut] =  "A" ||
		SAP[Attribut] = "B" ||
		SAP[Attribut] = "E"
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Any idea how to get this to work using the example above?

 

(Attribut <> "E" AND Status < 4 ) OR (Attribut = "B" AND Status = 3)

 

POS	Attribut	Status		Value
1		A	1		10
2		B	3		200
3		C	1		3000
4		D	4		40000
5		E	2		500000
6		A	4		10
7		B	1		200
8		C	2		3000
9		D	1		40000
10		E	4		500000

 

KHorseman
Community Champion
Community Champion

@potapthe syntax is the same. You can use parentheses. && is the AND operator, || is the OR operator.

 

CALCULATE(
	SUM(TableName[Value]),
	FILTER(
		TableName,
		(TableName[Attribut] <> "E" &&
		TableName[Status] < 4) ||
		(TableName[Attribut] = "B" &&
		TableName[Status] = 3)
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It was really helpful. 

You mentioned that this is one of your favourite DAX patterns. Could you please share your post or article with your other favourite DAX patterns? 

@vyacheslavg I'm having trouble with this unless I'm going mad I have created a very simple calculate DAX measure with an or filter and it's not working properly. 

 

I know the result is:

1001 = 36

1002 = 13

both = 49

 

My measure is: 

Members follow up or regular appointment = CALCULATE(DISTINCTCOUNT('Appointments'[Member]),
FILTER(
    'Appointments',
    'Appointments'[Type]="1001"||'Appointments'[Type]="1002"))

And the measure result is 36.

 

HELP!

Hi @vyacheslavg DISTINCTCOUNT is expecting to return a single value in a calculated measure. What is the end result/visual you need the information in? This can easily be done in a table or other visuals without DAX.

 

Thanks,

Dan

@dtartaglia Hi, so the distinctcount is counting unique instances of a member id, where the appointment type for the same row is 1001 or 1002.

This is because there are hundreds of appointments for each member and I want to count unique members who have had at least one appointment. 

 

Thanks!

Jemma

Hi  @vyacheslavg,

 

I'm using a very similar DAX measure in Desktop with slightly different syntax. I seem to get the correct number (created around 50 records). Please let me know if I'm missing something:

 

Members follow up or regular appointment = CALCULATE(DISTINCTCOUNT(Appointments[Members]), FILTER(Appointments, Appointments[Type] = "1001" || Appointments[Type] = "1002"))

 

DAXIssue.PNG

Hi @dtartaglia 

 

No, you're not missing something - I was. Clearly, I have members with an appointment with BOTH codes hence it's not adding the 13 and 36 exactly. I was expecting 49 but your example shows me what is actually happening here. 

 

Thank you so much, I really appreciate it! 🙂 

Jemma

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.