Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Stuznet
Helper V
Helper V

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.

 

2018-09-22_10-29-01.png

 

Back in Excel, I created 2 fields month and Plan Start manually. 

For instance, I count the IDs filtered with Plan Month = April, Plan Year= 2018, SavedDate = March RETURN April = 10. 

Another example, I count the IDs filtered with Plan Month = May, Plan Year= 2018, SavedDate = April RETURN May= 100. 

 

2018-09-22_10-08-49.png

 

I would like to know in power bi what would be the correct function for me to create a new calculated column or measure with different filters? or should created multiple measures? 

 

1 ACCEPTED SOLUTION

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:

16.PNG

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.

17.PNG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

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:

16.PNG

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.

17.PNG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much @v-lili6-msft@Greg_Deckler it worked 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.