cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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

3 REPLIES 3
Regular Visitor

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

Super User

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?

Super User

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors