Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Create a filter with a measure.

Hello all!  Is there a way to create a filter in the form of a measure? I want to write like this: Measure = CALCULATE( expression, [Filter Measure] ) Is there a similar way to do ...
Need Help
Kumail's avatar
Kumail
Icon for Impactful Individual rankImpactful Individual
5 years ago

Hello Anonymous 

 

Yes, definitely.

 

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

 

I hope this helps.

 

Regards
Kumail Raza

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Kumail,Thank you for helping!

I have many different fomulas and I want to calculate all of them for YTD, YearBefore and FirstYear like this:
 

YTD:
CALCULATE(
    expression,
    'Calendar'[Year] = YEAR( SELECTEDVALUE( 'Calendar'[Quarter-Year] ) ),
    'Calendar'[Quarter] <= QUARTER( SELECTEDVALUE( 'Calendar'[Quarter-Year] ) )
)

YearBefore:
CALCULATE(
    expression,
    'Calendar'[Year] = YEAR( SELECTEDVALUE( 'Calendar'[Quarter-Year] ) ) - 1,
)

FirstYear:
CALCULATE(
    expression,
    'Calendar'[Year] = MIN( 'Calendar'[Year])
)


The user is supposed to be able to choose year and the filters need to change accordingly (at least the first two).
I hope you understand what I mean 🙂

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    If the user selects the date using the slicer, then you can create a measure that takes in that date and replace that in the dax code that you refered.

     

    I hope this helps (Would love to see Kudo) 

     

    If you want me to write you a solution, just send over a short sample .pbix file.

     

    Regards

    Kumail Raza

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Kumail Wouldn't mind giving you a kudo if you helped me with the measure! ğŸ˜Š
      How do I sent a pbix-sample? Sorry, I am new to this. 

      Productivity with Variables = 
VAR SelectedYTD = 
    FILTER( 
        ALL('Calendar'[Quarter],'Calendar'[Year]),
        AND(
            'Calendar'[Year] = SELECTEDVALUE( 'Calendar'[Year] ),
            'Calendar'[Quarter] = SELECTEDVALUE( 'Calendar'[Quarter] )
        )
    )
VAR YearBefore =
    FILTER(
        ALL( 'Calendar'[Year] ),
        'Calendar'[Year] = SELECTEDVALUE( 'Calendar'[Year] ) - 1
    )
VAR MHworked = 
    [Man-hours worked - Staff registered Actual SUM] 
        + [Man-hours worked - Workers registered Actual SUM]
        + [Manhours worked agency people SUM]
VAR VAatSC = [Value added at SC SUM]



RETURN
DIVIDE(
    DIVIDE(
        CALCULATE(
            VAatSC,
            KEEPFILTERS( SelectedYTD )
        ),
        CALCULATE(    
            MHworked,
            KEEPFILTERS( SelectedYTD )
        ),
        0
    ),
    DIVIDE(
        CALCULATE(
            VAatSC,
            KEEPFILTERS( YearBefore )
        ),
        CALCULATE(    
            MHworked,
            KEEPFILTERS( YearBefore )
        ),
        0
    )
)
*100

      I've made this measure. However, it always return 100. It is probably something about the row-context that I don't understand. And I want to recreate the filters with measures instead so that I don't have to have the first 20 rows in each measure I do. 

      Thank you very much for your help. I really appreciate it.

      • Kumail's avatar
        Kumail
        Icon for Impactful Individual rankImpactful Individual
        5 years ago

        Hello Anonymous 

         

        You can upload it to the drive and send the link here. 

         

        I hope this helps.

         

        Regards

        Kumail Raza