Write a Measure to retrieve a Column Value based on other measure filters

[sudheer348]

Hi,

     I am drawing a Line chart with x-axis as Date and Y-axis as Percentage and would like to draw a vertical line (date) where i hit first 0 for "Remaining" or "Percentage" [ ideally both are similar ]. In this context, i want to create a Measure to capture this date so that i can use while configuring using "x-axis constant line"

    Question:
a. How to create a Measure to retrive first occurance of 0 in remaining ( want it to be generic so that i can use any other value later say: Remaining = 1 ). Btw, Remaining, Percentage are all Measures

b. Ignore all records with remaining =0 after first occurance ( in sense my table should have only one Remaining =0 and rest should be ignored)

c. Is there a way to include a sample row in this table ( if first row where Percentage =100 % is missing, can i statistically include it or should i work with data team to send it in all cases )

Thought of creating a table with all 0's and gather minimum date after applying the filters. But did not work

VAR _table = CALCULATETABLE(Table1, FILTER(Table1, Table1[M_Remaining] = 0 ))

M_MaxDate_0 =

CALCULATE(

        Max(Table1[MaxDate]),

        ALLSELECTED(Table1[Col1], Table1[Col2]),    <-- These are filters before retrieving the above table

        FILTER(Table1, Table1[M_Remaining] = 0)

        )

Thanks for all the help or direction!

Data.txt

[sudheer348]

Updated the sample Data Link now and it should not ask for login. thanks for catching it!

[lbendlin]

a) 

First Zero Date = 
var a = summarize(ALLSELECTED(Data),Data[MaxDate],"pc",min(Data[Percentage]))
return minx(filter(a,[pc]=0),[MaxDate])

b)

Show = if(max(Data[MaxDate])<=[First Zero Date],1,0)

not sure what you mean by c)  - Divide Remaining by Percentage should give you the full value?

[lbendlin]

link to sample data requires login