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

    )

Willborn's avatar
Willborn
Advocate III
9 years ago

Hi there

 

Thanks for this solution - it guided me a step further with a filter challange :smileyhappy:

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

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)
	)
)
      • 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?