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

    )

dtartaglia's avatar
dtartaglia
Resolver I
9 years ago

Hi KHorseman,

 

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

 

Thanks,

Dan

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