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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

17 REPLIES 17
Google5ive
Frequent Visitor

What if you have a multiples tables that need produce a Sum only if they have a value above 0 for example and I know this code isnt correct but im trying to explain

 

Calculate( Sum(
if Column 1 > 0,

if Column 2 > 0,

if Column 3 > 0)

 

 

How would I write something in the proper format in DAX

 

since i cant use if statements

 

This is what im trying to convert into DAX

 

SUM(iif((Fields.ADDTL_INST > 0
Or Fields.ADDTL_REF > 0
Or Fields.EXCISE_TAX > 0
Or Fields.RECORDING_FEES > 0
Or Fields.NON_STANDARD_FEES > 0)

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!




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 

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

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

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

Anonymous
Not applicable

@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

Anonymous
Not applicable

@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

Anonymous
Not applicable

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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.