Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello together,
i am looking for a way to show blanks as zero, but only between the first and last date which have Values > 0.
Usually i do it with +0 but then i have the problem, that every value in calendar gets a 0.
i need something like a for loop:
for all dates which are > calculate(min(date), value > 0) && < calculate(max(date), value > 0),
value + 0,
value
Thanks for help!
Nico
@Anonymous , Try like
0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))
doesn't get the expected solution.
Hi, @Anonymous
Please check the following methods.
Example data:
Table:
Date = CALENDAR(DATE(2022,1,1),DATE(2022,1,31))
Measure:
Measure =
VAR N1 =
SUM ( 'Table'[Value] ) + 0
VAR Mindate =
CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
VAR Maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
RETURN
IF (
SELECTEDVALUE ( 'Date'[Date] ) >= Mindate
&& SELECTEDVALUE ( 'Date'[Date] ) <= Maxdate,
N1,
BLANK ()
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for answering!
I think its the right way to go, but i have one problem left. In your example there ist no dimension table (except date table) to filter the fact table. But i have another column in my fact table which i have to filter with another dimension table. What now happens is that only the dates from the fact table get a zero as blank (which is good for the moment) but the measure does not recognize the min and max date for a specific dimension filter. This means that the minimum date value in fact table always gets a zero even if the minimum date for a specific dimension filter is much later.
Do you know a solution for this problem?
many many thanks!
Nico
Hi, @Anonymous
Can you provide a simple example file? Sensitive data can be provided for deletion. The desired result can also be shown with a picture.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Data model:
Result without a filter selection "Belegnummer" (as expected and correct):
Result with a filter selection "Belegnummer":
It starts at 31.12.2019 because thats the first date in table "Tätigkeiten". But with one filter selection in "Belegnummer" it should start at the first date, where a value is greater 0 for the specific "Belegnummer".
Hope it is understandable.
Thanks again
Nico
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
104 | |
98 | |
90 | |
71 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |