Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Goodday,
I have a problem and hope you guys can help. I have the following graph, where i replaced blank values with 0:
My goal is to make it like this
This Graph Follows a slicer that is on the page.
To make the graph more concise i am trying to keep the values before and after any data Blank, and between data i want it to display 0. As if this graph would start at 2021-12 and end at 2022-7, but dynamicly based on the slicer.
Based on the following link i made a measure to count it;
I have been fiddeling with the NOTS, <> and all other things to get it to work, but i am just not able. Do you guys have any idea how to solve this?
Solved! Go to Solution.
Dates is a dim that stores all dates from the 2000 year.
Graph Value =
var CurrentDate = SELECTEDVALUE( Dates[Date] )
var MinDateWithNonZeroData =
CALCULATE(
MINX(
FILTER(
Graph,
Graph[Value] > 0
),
Graph[Date]
),
ALLSELECTED( Dates )
)
var MaxDateWithNonZeroData =
CALCULATE(
MAXX(
FILTER(
Graph,
Graph[Value] > 0
),
Graph[Date]
),
ALLSELECTED( Dates )
)
var GraphValue =
if( MinDateWithNonZeroData <= CurrentDate
&& CurrentDate <= MaxDateWithNonZeroData
&& not ISBLANK( MinDateWithNonZeroData ),
CALCULATE(
SUM( Graph[Value] ) + 0,
TREATAS(
{ CurrentDate },
Graph[Date]
)
)
)
return
GraphValue
Thank you very much! It works great! I even got it working with just Year-Month Values
Dates is a dim that stores all dates from the 2000 year.
Graph Value =
var CurrentDate = SELECTEDVALUE( Dates[Date] )
var MinDateWithNonZeroData =
CALCULATE(
MINX(
FILTER(
Graph,
Graph[Value] > 0
),
Graph[Date]
),
ALLSELECTED( Dates )
)
var MaxDateWithNonZeroData =
CALCULATE(
MAXX(
FILTER(
Graph,
Graph[Value] > 0
),
Graph[Date]
),
ALLSELECTED( Dates )
)
var GraphValue =
if( MinDateWithNonZeroData <= CurrentDate
&& CurrentDate <= MaxDateWithNonZeroData
&& not ISBLANK( MinDateWithNonZeroData ),
CALCULATE(
SUM( Graph[Value] ) + 0,
TREATAS(
{ CurrentDate },
Graph[Date]
)
)
)
return
GraphValue
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
51 | |
45 | |
15 | |
12 |