How to create a calculate measure that can change dynamically with user selections on the report

[pandakillsalot]

Hi all, 

I create a measure to calculate the a value, and use date as a selection.

What I wish to achieve is when the date in the slicer is less than Jan 31, 2018, the measurement value is CALCULATE(DIVIDE([NumberPassesCorrect],[Unique SN count],0)) and when it 's later than that date, the value is one.

measurement = IF ([date] > DATE(2018, 1, 31) , CALCULATE(DIVIDE([NumberPassesCorrect],[Unique SN count],0)), 1 )

This line of code will give me an error saying the [date] must be an aggregate function instead.

Any thoughts? 

(here is a screenshot of my table)

[Phil_Seamark]

HI @pandakillsalot

Please try

New Column = 
    IF (
        MAX([date]) > DATE(2018, 1, 31) , 
        CALCULATE(
            DIVIDE([NumberPassesCorrect],[Unique SN count],0)), 1 )

[pandakillsalot]

Sorry, my bad. It should be a measurement instead of a column. I tried to add a Max function, but it ends up with turning everything to one regardless of the date chosen by slicer since the latest date on that table is certainly later than Jab 31.

[Phil_Seamark]

HI @pandakillsalot

What table does your [Date] field sit? 

[pandakillsalot]

In the same table with other measurements [NumberPassesCorrect],[Unique SN count]

[Phil_Seamark]

And what is that table called?  

[pandakillsalot]

The table is called StressProuduction and it's a pretty big one.

The FPY stands for first pass yield rate which is calculated by  (number of devices pass the test at the first time ) / (total number of devices that run).

After Jan 31, 2018, the FPY should always be 1.

My aim is to create a function which returns the results (number of devices pass the test at the first time ) / (total number of devices that run) before Jan 31, 2018 and returns 1 directly after that date.

[Phil_Seamark]

HI @pandakillsalot

Can you please send me a screenshot of your visual and of your table. This makes a difference to the way the calc should be written.

[pandakillsalot]

Sorry about that, I should have post a picture of the table

[Phil_Seamark]

ok, that helps.

And are [NumberPassesCorrect] and [Unique SN Count] both calculated measures over columns in that table?

I take it you use the StressProuduction[Date] column in your slicer?

[NumberPassesCorrect],[Unique SN count]

[pandakillsalot]

And what I trying to get is an graph like this  

[v-yuezhe-msft]

@pandakillsalot,

It seems that you want to create the following measure. Ensure that you create a measure rather than a calculated column.

 IF (
        MAX(StressProuduction[Date]) <= DATE(2018, 1, 31) , 
        CALCULATE(
            DIVIDE([NumberPassesCorrect],[Unique SN count],0)), 1 )

Regards,
Lydia

[pandakillsalot]

Thank you for your reply. Yes, that's what I try to do. But here is the problem, when the max date is 4/1 which is later than 1/31, all FPY will be 1 instead of calculating from the formula, and this will screw data before  1/31. I wish I could get both data before 1/31 and data after 1/31 showed correctly on the screen.

[v-yuezhe-msft]

@pandakillsalot,

I am not very clear about your requirement. Do you want to show data before 1/31/ and after 1/31 even if you make selections in slicer? Could you please share a dummy PBIX file here and post expected result here?

Regards,
Lydia

[pandakillsalot]

Yes all of them are measurements  over this table and I took the date as slicer