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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
igorabdo
Advocate II
Advocate II

last value with conditional (zero)

HI folks

 

I want to repeat the last value when the actual value is numeral zero.

I tried to use with earlier, but unsuccessfully.

 

 

Tks

Igor

1 ACCEPTED SOLUTION

Hi @igorabdo

 

If you want to have the average at the total there are two possibilities. I'm not sure which one you prefer.

One is that we take the average with the original zeros (i.e. without the filled-in values). Note that we are using the [Mesure_CUSTO] defined previously. You don't need to use it in the visual any longer but do keep the definition.

 

Measure_CUSTO_With_Avg@TotalWithOriginalZeros =
IF (
    ISFILTERED ( Table1[Mês Ano] );
    [Measure_CUSTO];
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( Table1; Table1[ITEMID]; Table1[Mês Ano] );
            "Result"; CALCULATE ( SUM ( Table1[CUSTO] ) )
        );
        [Result]
    )
)

 

 

View solution in original post

12 REPLIES 12
wbabuszewska
Regular Visitor

Hi, 
I am experiencing the same issue with the 0 value where i would like the measure to look for the last non zero value, I have used your measure above but it is not working do you know any reasons why this could be? 

AlB
Super User
Super User

Hi @igorabdo

 

Can you try to explain it a bit more?

Show the tables in your data model and example with the expected result to help explain what you want to do.

Hi @AlB

 

For example. In February/2018 the value is zero, but in January/2018 is 571,58. So, if is zero, I need to bring the last value (value <> 0)

Screenshot_6.png

@igorabdo

 

I get it now but i need more details to be able to work it out.

Can you share the pbix? That would be the quickest. Perhaps just with some dummy data if you have confidential data in it.

Otherwise:

1. What you are showing is a table visual? What are ITEMID, CUSTO, INVENTITEID and Mes Ano? Columns in your data table or measures? Is Mes Ano of type date or text?

2. If CUSTO is a measure I need to see its code.

3. Can you show a sample of your table(s) (not the visual, the table(s) itself). So that we can see the columns in it     

Hi @AlB

 

Example of PBIX

 

It's simple table.

I need to create a measure that, if you haven't the cost, bring the last value that isn't zero or null.

 

 

 

 

@igorabdo

Not trivial. You have to use an explicit measure instead of relying on an implicit one. Create this measure and place it in the table visual:

 

Measure_CUSTO = 
VAR _LatestNonZeroDate =
    CALCULATE (
        MAX ( Table1[Mês Ano] );
        FILTER (
            ALL ( Table1[Mês Ano] );
            Table1[Mês Ano] <= SELECTEDVALUE ( Table1[Mês Ano] )
                && CALCULATE ( SUM ( Table1[CUSTO] ) ) <> 0
        )
    )
RETURN
    CALCULATE (
        SUM ( Table1[CUSTO] );
        Table1[Mês Ano] = _LatestNonZeroDate
    )

 

It's perfect @AlB

The measure it's amazing.

 

The last question

If I want an average in the final?Screenshot_7.png

 

 

But don't worry. Your measure is perfect

Hi @AlB. Do you know what's happen?

Hi @igorabdo

 

If you want to have the average at the total there are two possibilities. I'm not sure which one you prefer.

One is that we take the average with the original zeros (i.e. without the filled-in values). Note that we are using the [Mesure_CUSTO] defined previously. You don't need to use it in the visual any longer but do keep the definition.

 

Measure_CUSTO_With_Avg@TotalWithOriginalZeros =
IF (
    ISFILTERED ( Table1[Mês Ano] );
    [Measure_CUSTO];
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( Table1; Table1[ITEMID]; Table1[Mês Ano] );
            "Result"; CALCULATE ( SUM ( Table1[CUSTO] ) )
        );
        [Result]
    )
)

 

 

Hi @AlB this is awesome !!

 

it worked well as a measure but can you help me for a column in the same logic. thanks !!

@igorabdo

 

The other option is that we take the average  with the filled-in values. Again, we are using the [Mesure_CUSTO] defined previously.

Let me know if this solves the issue.

 

Measure_CUSTO_With_Avg@TotalWithFilledValues2 = 
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( Table1; Table1[ITEMID]; Table1[Mês Ano] );
        "Result"; [Measure_CUSTO]
    );
    [Result]
)

@AlB
Perfect!!!!

The best solution.

 

 

Thanks very much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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