Forum Discussion
Measure changing the value after refresh
I'll try to explain first the problem that I have and then the informations that I'm able to show.
So, I have a mesaure that shows a calculation of the accumulated monthly default from some stores.
The measure has some rules, that you can see below:
VAR Default = CALCULATE(SUM(CF_INSTALLMENTS[TOTAL]); CF_INSTALLMENTS[TYPE] = "MAINTENANCE";CF_INSTALLMENTS[ACUM_HISTORIC_DEFAULT] = "HIST_DEFAULT")
VAR contract =
CALCULATETABLE (
VALUES ( CF_INSTALLMENTS[CONTRACT] );
FILTER (
CF_INSTALLMENTS;
CF_INSTALLMENTS[EXPIRATION_DATE_CONTRACT] >= MAX ( CF_INSTALLMENTS[LAST_DUE_DATE] )
)
)
RETURN
IF(Default > 0;
CALCULATE (
SUM ( CF_INSTALLMENTS[TOTAL] );
CF_INSTALLMENTS[TYPE] = "MAINTENANCE";
CF_INSTALLMENTS[ACUM_HISTORIC_DEFAULT] = "HIST_DEFAULT";
FILTER (
ALLSELECTED(CF_INSTALLMENTS);
CF_INSTALLMENTS[DUE_DATE] <= MAX ( CF_INSTALLMENTS[DUE_DATE])
&& CF_INSTALLMENTS[STATUS_CONTRACT_HIST] = "ACTIVE"
&& (CF_INSTALLMENTS[RECEIVEMENT_DATE] = BLANK()
|| CF_INSTALLMENTS[RECEIVEMENT_DATE] > MAX( CF_INSTALLMENTS[DUE_DATE]))
)
;
contract
);
BLANK())
As the measure is a little bit complex, it takes something about 6000ms until the line chart shows it values. The table "CF_INSTALLMENTS" has 1.3 million rows, unfortunately I can't share it.
The problem that I'm struggling in is that in Power BI Desktop the values of this line chart are correct, and when I upload for the first time (as a new report) in Power BI Service, it keeps the number also correct. However, when I refresh the Dataset via Power BI Service Gateway, the number changes a lot, for a number 2 millions higher (From ~6 million to ~8 million).
Power BI Desktop (correct):
Power BI Service (incorrect):
I tried to put the visual alone in a page to see if the problem was that I had a lot of visuals trying to refresh in the page, but it also did not work.
My Gateway Connection is with an SQL Server on-premises and it works well for all my reports.
My Power Query Editor M Code:
CF_INSTALLMENTS:
let
Fonte = Table.Combine({CF_OPEN_INSTALLMENTS, CF_PAID_INSTALLMENTS}),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"DATA_RECEBIMENTO", type date}, {"METODO_RECEBIMENTO", type text}, {"ROTA", type text}}),
#"Personalização Adicionada" = Table.AddColumn(#"Tipo Alterado", "SK_CONTRATO", each [LUC] & [CONTRATO]),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Personalização Adicionada",{{"SK_CONTRATO", type text}}),
#"Consultas Mescladas" = Table.NestedJoin(#"Tipo Alterado1", {"LUC"}, CF_STATUS_LUCS, {"LUC"}, "CF_STATUS_LUCS", JoinKind.LeftOuter),
#"CF_STATUS_LUCS Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas", "CF_STATUS_LUCS", {"STATUS_LUC"}, {"STATUS_LUC"}),
#"Consultas Mescladas1" = Table.NestedJoin(#"CF_STATUS_LUCS Expandido", {"SK_CONTRATO"}, CF_STATUS_CONTRATOS, {"SK_CONTRATO"}, "CF_STATUS_CONTRATOS", JoinKind.LeftOuter),
#"CF_STATUS_CONTRATOS Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas1", "CF_STATUS_CONTRATOS", {"STATUS_CONTRATO"}, {"STATUS_CONTRATO"}),
#"Consultas Mescladas2" = Table.NestedJoin(#"CF_STATUS_CONTRATOS Expandido", {"LUC", "CONTRATO"}, CF_DATA_EXPIRACAO_CONTRATO, {"LUC", "CONTRATO"}, "CF_DATA_EXPIRACAO_CONTRATO", JoinKind.LeftOuter),
#"CF_DATA_EXPIRACAO_CONTRATO Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas2", "CF_DATA_EXPIRACAO_CONTRATO", {"DATA_EXPIRACAO"}, {"DATA_EXPIRACAO"}),
#"Colunas Renomeadas" = Table.RenameColumns(#"CF_DATA_EXPIRACAO_CONTRATO Expandido",{{"DATA_EXPIRACAO", "DATA_EXPIRACAO_CONTRATO"}}),
#"Colunas Removidas1" = Table.RemoveColumns(#"Colunas Renomeadas",{"METODO_RECEBIMENTO", "NUMERO_TRANSACAO", "NOME_FANTASIA"})
in
#"Colunas Removidas1"
Does anyone have any idea what's going on and what I should do to fix it?
9 Replies
It mostly sounds like your Desktop pbix is not refreshed while your Service pbix is refreshed. Have you done a full refresh on your Desktop pbix before publishing to Service?
Yes, before publishing I refresh the dataset in Power BI Desktop.
The only other thing I can think of is confirming Filters match. Web browsers tend to remember the last Filter setting, so just make sure it's Reset to Default to match the Desktop version.
Hi MQS_Sistemas ,
Based on your description, a few questions need to be confirmed with you.
1. Do you see the query/table being refreshed in the pop-up window after you click the "Refresh" button? You need to check if the loading feature is disabled in the query editor.2. After refreshing, do you see new data in the report?
You need to check and clear filters working on your reports or visuals .Hope it helps,
Community Support Team _ Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.Hello, thanks for your help.
Yes, both thing I checked and they're working.
