topic Re: Subset table in DAX Commands and Tips

Subset table

Anonymous — Fri, 15 Jul 2022 12:01:49 GMT

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?

Re: Subset table

Jihwan_Kim — Fri, 15 Jul 2022 12:26:31 GMT

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

Re: Subset table

daXtreme — Sun, 17 Jul 2022 08:46:07 GMT

[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 ) )

Re: Subset table

Anonymous — Mon, 18 Jul 2022 09:11:04 GMT

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.