Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago
Solved

Combine according to condition

Hi,

Thanks for looking at my post.

 

I am looking for dax formula to do the below please?

Any help will be much appreciated 

 

I have year/month and name column and i wanted to combine the "Total" according to Year/Month and if is in "Name1,Name3 or Name5" and ignore the rest

 

Any idea please?

 

Thanks a lot 

 

  • Joe_Barry's avatar
    Joe_Barry
    2 years ago

    You need to reference the Year column so it should be

    Earlier('Table1'[Year])


    Please change out the Table1 part to the actual name of your table

6 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi jimpatel 

     

    Dax solution

     

    In the second table add a column and enter this measure

    Expected Result =
CALCULATE (
    SUM('Table1'[Total]), 
    FILTER (
        'Table1',
        'Table1' [Year] = 'Table2'[Year] &&
        'Table1' [Month] = 'Table2'[Month] &&
        'Table1' [Name] in  {"Name1, "Name3", "Name5"}
)

     

    Joe

     

    If you found my answer helpful and it solved your issue, please accept as solution

  • jimpatel's avatar
    jimpatel
    Icon for Post Patron rankPost Patron
    2 years ago

    Hi,

     

    Thanks a lot for your reply.

     

    May i know do i need to create second table for this measure? Can we not do it in the same table?

     

    Thanks a lot again

    • Joe_Barry's avatar
      Joe_Barry
      Icon for Solution Sage rankSolution Sage
      2 years ago

       

      Expected Result = 
IF( Table1[Name] in {"Name4", "Name2"} , BLANK(), 
CALCULATE(
SUM(Table1[Total]), 
FILTER('Table1', 
   'Table1'[Year] = Earlier('Table1'[Year]) && 
   'Table1'[Month] = Earlier('Table1'[Month]) &&
   'Table1'[Name] in  {"Name1", "Name3", "Name5"})))

       

      This should work, add a custom column and this measure

      • jimpatel's avatar
        jimpatel
        Icon for Post Patron rankPost Patron
        2 years ago

        Thanks a lot for your time.

         

        I am getting error in Earlier any idea please?