Forum Discussion

Mgg's avatar
Mgg
Icon for Helper I rankHelper I
5 years ago
Solved

Calculate with filter

Spoiler

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

  • Migasuke's avatar
    Migasuke
    5 years ago

    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!

  • Migasuke's avatar
    Migasuke
    5 years ago

    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.

7 Replies

  • Migasuke's avatar
    Migasuke
    Icon for Memorable Member rankMemorable Member
    5 years ago

    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!

    • Mgg's avatar
      Mgg
      Icon for Helper I rankHelper I
      5 years ago

      GREAT...problem solved

      Thanks for your support.

      Now the filter is working well ğŸ’ª

    • Mgg's avatar
      Mgg
      Icon for Helper I rankHelper I
      5 years ago

      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

      • Migasuke's avatar
        Migasuke
        Icon for Memorable Member rankMemorable Member
        5 years ago

        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.

  • Mgg's avatar
    Mgg
    Icon for Helper I rankHelper I
    5 years ago

    GREAT ...problem solved !!!

    Many thanks