Forum Discussion

sudheer348's avatar
sudheer348
Regular Visitor
3 years ago

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

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

Need Help
Show and Tell
Tutorial Requests

3 Replies

  • sudheer348's avatar
    sudheer348
    Regular Visitor
    3 years ago

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

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      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?