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
10 years ago

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