DAX Calculate IF OR Statement

[BKnecht]

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. 

[Google5ive]

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 · Accepted solution]

CALCULATE(

SUM(Table[Column X]),

FILTER(

Table,

Table[Column A] = "Renewal" ||

Table[Column B] = "Needs Alignment")

)

[Anonymous]

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]

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

[BKnecht]

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]

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.

[dtartaglia]

Hi KHorseman,

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

Thanks,

Dan

[Willborn]

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

[KHorseman]

@Willborn

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

[potap]

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]

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

[vyacheslavg]

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]

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

[dtartaglia]

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]

@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

[dtartaglia]

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

[Anonymous]

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