Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

Calculate Column With Filters

 Hi guys, I'm struggling with power bi DAX function. I have table consists of IDs, Plan Month, Plan Year, SavedDate.     Back in Excel, I created 2 fields month and Plan Start manually.  ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Stuznet

         You can try to use this formula

    AprilStartPlan =
SWITCH (
    TRUE (),
    MAX ( DateTable[month] ) = "April", CALCULATE (
        COUNT ( Data[ID] ),
        FILTER (
            Data,
            [Plan Month] = "Apr"
                && [Plan Year] = "2018"
                && [SavedDate] = "March"
        )
    ),
    MAX ( DateTable[month] ) = "March", CALCULATE (
        COUNT ( Data[ID] ),
        FILTER (
            Data,
            [Plan Month] = "Mar"
                && [Plan Year] = "2018"
                && [SavedDate] = "March"
        )
    )
)

    Result:

    here is pbix, please try it.

    https://www.dropbox.com/s/gdwrikrf2gkih4h/Calculate%20Column%20With%20Filters.pbix?dl=0

     

    By the way, your error that one ) is missing, there should be two ")" in this.

     

    Best Regards,

    Lin

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

Sooo, is there a pattern to that at all? If not, you could use a SWITCH TRUE statement like:

 

Measure = 
SWITCH(
  TRUE(),
  MAX([Month]) = "April", CALCULATE(COUNT([ID]),FILTER('Table',[Plan Month]="April" && [Plan Year] = 2018 && [SavedDate]="March"),
  MAX([Month]) = "May", CALCULATE(COUNT([ID]),FILTER('Table',[Plan Month]="May" && [Plan Year] = 2018 && [SavedDate]="April"),
)
  • Stuznet's avatar
    Stuznet
    Helper V
    7 years ago

    Greg_Decklerthank you for helping me. I used your provided solution but I'm getting an error 

     

    A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

    AprilStartPlan = SWITCH(TRUE(),
    MAX('DateTable[month])="April", CALCULATE(COUNT(Data[ID]),
        FILTER(Data,[Plan Month] = "April" &&
            [Plan Year] = "2018" &&
            [SavedDate] = "March"),
    MAX(DateTable[month]) = "May", CALCULATE(COUNT(Data[ID]),
        FILTER(Data,[Plan Month] = "May" &&
            [Plan Year] = "2018" &&
            [SavedDate] = "April"))))

     

     

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi, Stuznet

           You can try to use this formula

      AprilStartPlan =
SWITCH (
    TRUE (),
    MAX ( DateTable[month] ) = "April", CALCULATE (
        COUNT ( Data[ID] ),
        FILTER (
            Data,
            [Plan Month] = "Apr"
                && [Plan Year] = "2018"
                && [SavedDate] = "March"
        )
    ),
    MAX ( DateTable[month] ) = "March", CALCULATE (
        COUNT ( Data[ID] ),
        FILTER (
            Data,
            [Plan Month] = "Mar"
                && [Plan Year] = "2018"
                && [SavedDate] = "March"
        )
    )
)

      Result:

      here is pbix, please try it.

      https://www.dropbox.com/s/gdwrikrf2gkih4h/Calculate%20Column%20With%20Filters.pbix?dl=0

       

      By the way, your error that one ) is missing, there should be two ")" in this.

       

      Best Regards,

      Lin