Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subset table

I have a table

 

Table: Sales

Fruits; Year; Euros

Apple; 2020; 5000

Apple; 2021; 6000

Grape;2020; 1000

Grape; 2021; 2000

 

How can I select the value of Euros for Grape in 2021? 

 

I am creating a new measure which I would like to use part of the information from my table. 

 

Should I use Selectvalues, values or filter? 

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please try to create a measure like below and you can choose to use filter function or not :

    Measure with filter function:

    Measure1 =
CALCULATE (
    MAX ( Sales[Euros] ),
    FILTER ( ALL ( Sales ), Sales[Fruits] = "Grape" && Sales[Year] = 2021 )
)

     


    Measure without filter function:

    Measure2 =
CALCULATE (
    MAX ( Sales[Euros] ),
    Sales[Fruits] = "Grape",
    Sales[Year] = 2021,
    ALL ( Sales )
)

     

    If I misunderstand your demands, please feel free to let me know.

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please try something like below.

     

    Euros only for grape 2021 measure: =
CALCULATE (
    SUM ( 'Sales'[Euros] ),
    FILTER ( ALL ( 'Sales' ), 'Sales'[Fruits] = "Grape" && 'Sales'[Year] = 2021 )
)
  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago
    [what you want] =
calculate(
  selectedvalue( Sales[Euros] ),

  // keepfilters is here to honor
  // any already existing filters
  // on the table
  keepfilters( Sales[Fruit] = "Grape" ),
  keepfilters( Sales[Year] = 2021 ),

  // use all( Sales ) if you want to
  // remove all exising filters on Sales;
  // if you want to keep them, then remove
  // this line
  all( Sales )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please try to create a measure like below and you can choose to use filter function or not :

    Measure with filter function:

    Measure1 =
CALCULATE (
    MAX ( Sales[Euros] ),
    FILTER ( ALL ( Sales ), Sales[Fruits] = "Grape" && Sales[Year] = 2021 )
)

     


    Measure without filter function:

    Measure2 =
CALCULATE (
    MAX ( Sales[Euros] ),
    Sales[Fruits] = "Grape",
    Sales[Year] = 2021,
    ALL ( Sales )
)

     

    If I misunderstand your demands, please feel free to let me know.

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.