Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Nico78
Frequent Visitor

Blank as Zero but only between first and last date which have a value greater 0

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

6 REPLIES 6
amitchandak
Super User
Super User

@Nico78 , 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. 

Nico78_0-1656923828532.png

 

Nico78_1-1656923924067.pngNico78_2-1656923972404.png

 

Hi, @Nico78 

 

Please check the following methods.

Example data:

vzhangti_0-1657188404278.png     vzhangti_2-1657188458100.png

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 ()
    )

vzhangti_3-1657188571672.png

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, @Nico78 

 

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: 

Nico78_0-1657528368757.png

Result without a filter selection "Belegnummer" (as expected and correct):

Nico78_1-1657528569510.png

 

Result with a filter selection "Belegnummer": 

 

Nico78_2-1657528671441.png

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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