Create a filter with a measure.

[Anonymous]

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 this?
Otherwise I will have to copy and paste my filter everywhere and it seems like there should be an easier way.

Thank you for taking your time to do this!

[Anonymous]

Hi @Anonymous ,

To my knowledge, you need to create a table with YTD, YearBefore,FirstYear for slicer :

Then use SWITCH() to calculate different expressions like this:

Measure = SWITCH(MAX('ForSlicer'[Type]),"YTD",
CALCULATE(
   SUM('Calendar'[Value]),
    FILTER('Calendar','Calendar'[Year] =MAX('Calendar'[Year]) &&
    'Calendar'[Quarter] <= MAX('Calendar'[Quarter]))),

"YearBefore",
CALCULATE(
  SUM('Calendar'[Value]),
   FILTER(ALL('Calendar'), 'Calendar'[Year] =MAX('Calendar'[Year]) - 1)),

"FirstYear",
CALCULATE(
    SUM('Calendar'[Value]),
    FILTER('Calendar','Calendar'[Year] = MIN( 'Calendar'[Year])))
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, 
Thank you for taking your time to respond to this. 
What I need to do is to have it dynamic so the table would have to change when the user selects different values in a slicer. 
If the user chooses Q3 2019 then it should show YTD for 2019 compared to entire 2018. 
If the user chooses Q1 2020 then i should show the first quarter 2020 compared with 2019. 

Will this work with your solution? 

[Kumail]

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]

@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]

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]

@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]

Hello @Anonymous 

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

I hope this helps.

Regards

Kumail Raza

[Anonymous]

@Kumail What if the data is confidential?
Do I rewrite the entire file?
I apologize for my insufficient knowledge.

[Kumail]

@Anonymous Don't worry 🙂

You can annoymize the data and just send the drive URL to the sample file.

Regards

Kumail Raza

[Anonymous]

@Kumail Okay how do I do that if the data set comes in as a directquery?

[Kumail]

@Anonymous 

The best thing here is to branch out your scenario data into a separate file.

You can filter down the data into very small amount and then export it to excel and then reload the data into another model and set it up exactly the same way as in your original model.

The good thing about it is the techniques are exactly the same, you can have just 0.1% of data or even less and it will exactly be the same solution. 

Hope this helps.

Regards

Kumail Raza

MS Power BI Community: community.powerbi.com/t5/user/viewprofilepage/user-id/22640