Measure not working after refresh

[andicornejo12]

Hello Community,

I need your help please. 

I have built a formula which basically calculates something different depending on which selection you have done to a slicer hierarchy.

So for example it calculates something when a level 2 in a hierachy is chosen and something different when is level 1. The formula seems to work completely fine in dektop but for some strange reason, as soon as I publish and the data gets refreshed, the formula is not working any longer. See below images. I have left visible only the line that is relevant:

Published version: ILV is zeroRefreshed version: ILV is displayable but it should be zero

The first image shows the table wo ILV columns as zeros and that is how I publish and should look in service, the second image however is after the data gets refreshed in service and the wo ILV columns instead of zero are displayable and showing.

This is the formula I am using for when the ILV should show zero:

Actuals wo ILV =

IF(

    ISFILTERED('Hierarchy'[Segment Name]), [Actuals]-CALCULATE([Actuals],'Snapshot EBIT'[ILV Segment]="TRUE"),

    IF(

        ISFILTERED('Hierarchy'[Business Area Name]),[Actuals]-CALCULATE([Actuals],'Snapshot EBIT'[ILV BA]="TRUE"),

        IF(

            ISFILTERED('Hierarchy'[Business Unit Name]),[Actuals]-CALCULATE([Actuals],'Snapshot EBIT'[ILV BU]="TRUE"),

    [Actuals]-CALCULATE([Actuals],'Snapshot EBIT'[ILV Company]="TRUE"))))

What am I doing wrong? or whz is teh formula not working after pusblishing?

Thanks so much for the help!

[johnt75]

When working with hierarchies, although ISFILTERED may work you are safer using ISINSCOPE, because that isn't affected by any other filters which may be applied to the column from other visuals or the filter pane. Using ISINSCOPE you need to check the lowest level of the hierarchy first, so you would have something like

My Measure =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Hierarchy'[Level 3] ), 3,
    ISINSCOPE ( 'Hierarchy'[Level 2] ), 2,
    ISINSCOPE ( 'Hierarchy'[Level 1] ), 1,
    0
)

That may or may not be causing the issue here. The first thing I would do is refresh the data in Power BI desktop and see if that still shows what you expect it to.

If it does, then I would change the measure for debugging and have each clause of the IF or SWITCH statement return a different number so that you can see which clause is being triggered. Publish that and see if the same clause is being triggered after refresh. That would at least give you an idea as to where the problem lies.

[andicornejo12]

Hi @johnt75 

Thank you so much for getting back to me.

I have followed your recommendation and update the formula with your proposed expressions SWITCH instead of IF and ISINSCOPE instead of ISFILTERED. Unfortunately, I am experiencing the same issue with it as well. Works prefectly in desktop but not after published in service.

I also refreshed in desktop and after refresh in desktop is still showing me what I would expect, meaning that is working. Not sure what happens in teh refresh in service that is defferent to the desktop refresh.

Also I have broken down the formula in clauses by hierachy and in all 3 levels I have teh same error 😞

Is it something about the condition "TRUE" in my if true results formula i.e. 

[Actuals]-CALCULATE([Actuals],'Snapshot EBIT'[ILV BU]="TRUE") ?

[johnt75 · Accepted solution]

It could be the TRUE conditions. Check the types of the columns you are comparing against. If the column is text, then "TRUE" with the double quotes is correct. If the column is a Binary data type then you would need to remove the double quotes.

If you are publishing the report to a premium or fabric capacity, then you could use DAX Studio to connect to the live semantic model and run queries against that. Get the query for the visual by using Performance Analyzer and run it against both the local and live models, see where the differences lie. DAX Studio would also allow you to examine the data in the live model, and compare against what you have locally.

[andicornejo12]

Dear @johnt75 

Thank you so much!

It was as stupid as making sure that the "TRUE" columns were in fact type Text. They were type Any and as true/false values they were converting it as 0 and -1 (microsoft default behavior). 

Therefore when converted to Text, the formula didn't break anymore after refresh.