cancel
Showing results for
Did you mean:
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
Community Champion

CALCULATE(

SUM(Table[Column X]),

FILTER(

Table,

Table[Column A] = "Renewal" ||

Table[Column B] = "Needs Alignment")

)

Proud to be a Super User!

17 REPLIES 17
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

Or Fields.EXCISE_TAX > 0
Or Fields.RECORDING_FEES > 0
Or Fields.NON_STANDARD_FEES > 0)

Community Champion

CALCULATE(

SUM(Table[Column X]),

FILTER(

Table,

Table[Column A] = "Renewal" ||

Table[Column B] = "Needs Alignment")

)

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?

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

Community Champion

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

Proud to be a Super User!

Helper II

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!

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.

Proud to be a Super User!

Resolver I

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

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

Community Champion

@Willborn

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

Proud to be a Super User!

New Member

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```

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)
)
)```

Proud to be a Super User!

Helper II

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!

Resolver I

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

Resolver I

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"))

Anonymous
Not applicable

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors