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(

SUM(Table[Column X]),

FILTER(

Table,

Table[Column A] = "Renewal" ||

Table[Column B] = "Needs Alignment")

)

BKnecht's avatar
BKnecht
Kudo Kingpin
10 years ago

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!

  • 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