Forum Discussion
Measure not working after refresh
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.
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.
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") ?
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.
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.