Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX Calculate IF OR Statement

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

DAX Calculate IF OR Statement

05-19-2016
05:48 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-19-2016
06:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-10-2022
12:36 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-19-2016
06:34 AM

CALCULATE(

SUM(Table[Column X]),

FILTER(

Table,

Table[Column A] = "Renewal" ||

Table[Column B] = "Needs Alignment")

)

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-16-2020
05:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-16-2020
06:17 AM

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

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-19-2016
06:46 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-19-2016
07:05 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-23-2016
12:07 PM

Hi KHorseman,

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

Thanks,

Dan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-10-2016
05:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-15-2016
07:55 AM

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

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-04-2017
10:23 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-04-2017
11:04 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-22-2017
06:27 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-12-2019
07:05 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-12-2019
08:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-12-2019
08:27 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-12-2019
09:24 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-13-2019
01:03 AM

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