Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

GROUPBY, FILTER, CALCULATE

Hello

 

I have this table:

 

Col1Col2
A01/01/2000
A02/01/2000
A03/01/2000
B04/01/2000
B05/01/2000

 

I want to calculate the Earliest Date for A, B etc (i.e. per Col1 value)

 

How does that work? Is the below the proper way and how do I use multiple criteria for grouping?

Earliest Date = CALCULATE ( MIN ( Data[Col2] ), Data[Col1])

 

Also, is there a FILTER, CALCULATE, GROUPBY  etc comprehensive tutorial with advanced use cases?

 

Last, which is the fastest and more efficient?

 

Thanks!

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    Column = 
CALCULATE (
    MIN ( Data[Col2] ),
    FILTER ( Data, [Col1] = EARLIER ( Data[Col1] ) )
)

    Measure:

    Measure = 
CALCULATE (
    MIN ( Data[Col2] ),
    FILTER (ALL( Data), [Col1] = SELECTEDVALUE( Data[Col1] ) )
)

    The above two methods are some of the differences when using FILTER, CALCULATE, EARLIER and SELECTEDVALUE play the role of grouping in the calculation. For computation, MEASURE is more efficient.

     

    Best Regards,

    Community Support Team _Charlotte

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

3 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hey Anonymous ,

     

    if you want to have in general the earliest date of all rows, you can use the following approach:

    Earliest Date of all = CALCULATE ( MIN ( Data[Col2] ), ALL ( Data[Col1] ) )

     

    If you want to earliest date per value in Col1, then the following approach will help you:

    Earliest Date of all = CALCULATE ( MIN ( Data[Col2] ), ALLEXCEPT ( Data, Data[Col1] ) )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me: twitter.com/DenSelimovic
     

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    why not just use MIN 

    Measure = min('Table'[Col2])
    if you add a filter, that will also work.
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    Column = 
CALCULATE (
    MIN ( Data[Col2] ),
    FILTER ( Data, [Col1] = EARLIER ( Data[Col1] ) )
)

    Measure:

    Measure = 
CALCULATE (
    MIN ( Data[Col2] ),
    FILTER (ALL( Data), [Col1] = SELECTEDVALUE( Data[Col1] ) )
)

    The above two methods are some of the differences when using FILTER, CALCULATE, EARLIER and SELECTEDVALUE play the role of grouping in the calculation. For computation, MEASURE is more efficient.

     

    Best Regards,

    Community Support Team _Charlotte

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