topic Re: Calculate with filter in DAX Commands and Tips

Calculate with filter

Mgg — Thu, 08 Jul 2021 22:24:21 GMT

Hello,

I'm in a trouble with a filter.

I need to show a score filtering the data using a measure (not fixed data)

I'm using the following but it doesn't work:

CALCULATE([SpeScore], FILTER(SPE, SPE[Period]=[SpeLastPeriod]))

Anyone can help me?

Thans in advance

Re: Calculate with filter

Migasuke — Thu, 08 Jul 2021 23:05:58 GMT

Hi,
If I got you right, you trying to calculate SpeScore for latest value.
Unfortunatelly this does not work but there is an easy.

1. In your SPE table, create a calculated column with MAXX and IF, where you specify the latest (period, date, year...)

2. Use your DAX measure, only adjust the condition bit, something like this:

As you can see, I get the value for Period 5, which is the latest.

Please let me know, how did it help!

Re: Calculate with filter

Mgg — Fri, 09 Jul 2021 08:33:15 GMT

GREAT ...problem solved !!!

Many thanks

Re: Calculate with filter

Mgg — Fri, 09 Jul 2021 08:34:59 GMT

GREAT...problem solved

Thanks for your support.

Now the filter is working well 💪

Re: Calculate with filter

Mgg — Fri, 09 Jul 2021 09:32:31 GMT

Thanks for your solution but I have an additional complexity: How I can define the "Latest" period taking in consideration also the "Company"?

In the example the latest period for the companies B and D is 2 and NOT 3 like for companies A and C

Re: Calculate with filter

Migasuke — Fri, 09 Jul 2021 17:02:58 GMT

Hi Mgg ,
That depends how you trying to visualize the measure or what should be the output.
For example, you can use your measure with Company Slicer and everything will work well.

Re: Calculate with filter

Mgg — Fri, 09 Jul 2021 17:08:58 GMT

I'm doind that but its showing always the latest period and so, if the company there isn't in that period (eg Company B period 3) it shoed score "blank.

I'd like to label the latest period x company and not the latest per colum like it is.

Re: Calculate with filter

Migasuke — Fri, 09 Jul 2021 17:13:38 GMT

Hi,
It should work fine, if you use column, which has "Latest" by Company, check this:

The measure then will be a bit adjusted:

Latest Value =

CALCULATE(SUM(SPE[Value]),SPE[Latest In Group]="Latest")

Once you use slicer, it will behave the way you want.