Forum Discussion

BKnecht's avatar
BKnecht
Kudo Kingpin
10 years ago
Solved

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:   Calcula...
  • Anonymous's avatar
    Anonymous
    10 years ago

    CALCULATE(

    SUM(Table[Column X]),

    FILTER(

    Table,

    Table[Column A] = "Renewal" ||

    Table[Column B] = "Needs Alignment")

    )

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Willborn

 

Total_ABE = CALCULATE(
	SUM(SAP[Value]);
	FILTER(
		SAP;
		SAP[Attribut] =  "A" ||
		SAP[Attribut] = "B" ||
		SAP[Attribut] = "E"
	)
)
potap's avatar
potap
New Member
9 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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)
	)
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    dtartaglia
    Resolver I
    6 years ago

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

     

  • vyacheslavg's avatar
    vyacheslavg
    Helper II
    9 years ago

    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? 

  • dtartaglia's avatar
    dtartaglia
    Resolver I
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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