Forum Discussion

4-3-3's avatar
4-3-3
Icon for Helper I rankHelper I
6 years ago
Solved

How do you GROUPBY two collumns with DAX?

I have a table that has multiple records for each day of the year for multiple entities. I need to create a column that will flag the newest record for each day as per the below example. Can someone ...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi 4-3-3 ,

    I modified my formula that you can have a try.

    Flag = 
var max_recorded = CALCULATE(MAX('Table'[recorded]),ALLEXCEPT('Table','Table'[date]))
return
IF('Table'[recorded] = max_recorded,"Yes",BLANK())

     

    Best Regards,

    Xue Ding

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

4-3-3's avatar
4-3-3
Icon for Helper I rankHelper I
6 years ago

Thanks for your input. Unfortunatetly that example only grouping by one column and I need this to look at two..

v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi 4-3-3 ,

I created a measure that may help you. 

Measure = 
var max_recorded = CALCULATE(MAX('Table'[recorded]),ALLEXCEPT('Table','Table'[branchID]))
return
IF(MAX('Table'[recorded]) = max_recorded,"Yes",BLANK())

Best Regards,

Xue Ding

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

  • 4-3-3's avatar
    4-3-3
    Icon for Helper I rankHelper I
    6 years ago

    Hi there, thanks for trying to help, it is much appreciated. Unfortunately this is not doing what I am after.

     

    It measure needs to be looking on two columns - hence the question about using two columns in a group-by formula as that is how I interpret it in my head but it may not be the correct term..

     

    Anyway,  I have prepared a better file that will hopefully be of a help to explain what I am after - i have added the 'latestFlag' column to mark the lines exactly how I need them to be.

     

    https://radvisory-my.sharepoint.com/:x:/g/personal/tom_radvisory_co_nz/EY7ssyFpZEZDpbPIvQbWufcB1vFwSQUTQj0zdx90LzXKiA?e=XXb91i 

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi 4-3-3 ,

      I modified my formula that you can have a try.

      Flag = 
var max_recorded = CALCULATE(MAX('Table'[recorded]),ALLEXCEPT('Table','Table'[date]))
return
IF('Table'[recorded] = max_recorded,"Yes",BLANK())

       

      Best Regards,

      Xue Ding

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

      • 4-3-3's avatar
        4-3-3
        Icon for Helper I rankHelper I
        6 years ago

        Beautiful! I did not realize that I can actually leave out the branchID from the measure all together as I can just use filter later on.

         

        Thanks so much for helping!