topic Re: Measure dissapers when published file refreshes in DAX Commands and Tips

Measure dissapers when published file refreshes

Dwivedi15 — Tue, 21 Jan 2025 15:17:28 GMT

I have two measures :

ActualMeasure_HD =

(

CALCULATE(

SUM(CombinedTable[Amount]),

CombinedTable[Source] = "Actual"

)

and

BudgetMeasure_HD =

(

CALCULATE(

SUM(CombinedTable[Amount]),

CombinedTable[Source] = "Budget"

)

actual measure works fine after report is published and refreshed but budget measure dissapers. Both are in same table and are collecting data from same table.
Although this table is taking rows - from Actual ledger with additional column Source = Actual abd budget table with additional column Source= Budget.
This table is just union of other two tables in dax. (both have alligned columns and number of columns are also same)

Please help. 🙂

Re: Measure dissapers when published file refreshes

nilendraFabric — Tue, 21 Jan 2025 16:34:27 GMT

Hello Dwivedi15

Try adding an ALL() function to your measure to ignore any potential conflicting filter contexts:

BudgetMeasure_HD =
CALCULATE(
SUM(CombinedTable[Amount]),
ALL(CombinedTable),
CombinedTable[Source] = "Budget"
)

please see if this works, else we will look for other workarounds.

thanks

Re: Measure dissapers when published file refreshes

Anonymous — Wed, 22 Jan 2025 01:58:40 GMT

Hi, Dwivedi15

Could you tell me what’s your data connection mode (import, live connect or direct query) ?

May I ask if the DAX provided by nilendraFabric has solved the problem you are experiencing?

According to your description, You can check to see if there are any filters applied to your report or visualization charts that may affect the budget measure. sometimes filters can inadvertently exclude data.

Otherwise, would you like to share a sample pbix file you used(without sensitive data) and we can help you in advance.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Measure dissapers when published file refreshes

Dwivedi15 — Wed, 22 Jan 2025 09:45:28 GMT

Hi I tried this :

BudgetMeasure_HD =

VAR BudgetAmount =

CALCULATE(

SUM(CombinedTable[Amount]),

CombinedTable[Source] = "Budget"

)

RETURN

IF(

ISBLANK(BudgetAmount),

BudgetAmount

)

Now after this published file gets refreshed- I can see budegt measure is only working for january data . Other all months become zero

Re: Measure dissapers when published file refreshes

Dwivedi15 — Wed, 22 Jan 2025 09:50:03 GMT

hi, there are no Filters in transformations when data is brought into the system - Table A and B are coming as it is in the system.

Then I am making a Combined table :

CombinedTable =

UNION(

SELECTCOLUMNS(

'LMAM General Ledger',

"EntryNo",'LMAM General Ledger'[EntryNumber],

"GL Account Number", 'LMAM General Ledger'[G_L_Account_No],

"Date", 'LMAM General Ledger'[Posting_Date],

"Department", LOOKUPVALUE('LMAM Dimension Sets'[DEPT],'LMAM Dimension Sets'[Dimension_Set_ID],'LMAM General Ledger'[Dimension_Set_ID]),

"Maintenance Type",LOOKUPVALUE('LMAM Dimension Sets'[MAINTENANCE TYPE],'LMAM Dimension Sets'[Dimension_Set_ID],'LMAM General Ledger'[Dimension_Set_ID]),

"Utility",LOOKUPVALUE('LMAM Dimension Sets'[UTILITY],'LMAM Dimension Sets'[Dimension_Set_ID],'LMAM General Ledger'[Dimension_Set_ID]),

"Category Ordered",LOOKUPVALUE('LMAM GL Code Status'[CategoryOrdered],'LMAM GL Code Status'[number],'LMAM General Ledger'[G_L_Account_No]),

"SubCategory",LOOKUPVALUE('LMAM GL Code Status'[subCategory],'LMAM GL Code Status'[number],'LMAM General Ledger'[G_L_Account_No]),

"Code+Desc",LOOKUPVALUE('LMAM GL Code Status'[Code + Desc],'LMAM GL Code Status'[number],'LMAM General Ledger'[G_L_Account_No]),

"DimID", 'LMAM General Ledger'[Dimension_Set_ID],

"Amount",'LMAM General Ledger'[Amount],

"Source", "Actual"

SELECTCOLUMNS(

'Master LMAM GL Budgets',

"EntryNo",'Master LMAM GL Budgets'[Entry_No],

"GL Account Number", 'Master LMAM GL Budgets'[G_L_Account_No],

"Date", 'Master LMAM GL Budgets'[NormalizedDate],

"Department",'Master LMAM GL Budgets'[LMAM Dimension Sets.DEPT],

"Maintenance Type",LOOKUPVALUE('LMAM Dimension Sets'[MAINTENANCE TYPE],'LMAM Dimension Sets'[Dimension_Set_ID],'Master LMAM GL Budgets'[Dimension_Set_ID]),

"Utility",'Master LMAM GL Budgets'[LMAM Dimension Sets.UTILITY],

"Category Ordered",LOOKUPVALUE('LMAM GL Code Status'[CategoryOrdered],'LMAM GL Code Status'[number],'Master LMAM GL Budgets'[G_L_Account_No]),

"SubCategory",LOOKUPVALUE('LMAM GL Code Status'[subCategory],'LMAM GL Code Status'[number],'Master LMAM GL Budgets'[G_L_Account_No]),

"Code+Desc",LOOKUPVALUE('LMAM GL Code Status'[Code + Desc],'LMAM GL Code Status'[number],'Master LMAM GL Budgets'[G_L_Account_No]),

"DimID", 'Master LMAM GL Budgets'[Dimension_Set_ID],

"Amount",'Master LMAM GL Budgets'[Amount],

"Source", "Budget"

)

)

So that i can get all data from single source which solves my issue of having two seperate date columns. No i have 1 , which makes my life easier.

these measure are working all fine pbix file. n when i publish them- its all good, the moment refresh happens- data from budegt measure goes away from all the months except january

Re: Measure dissapers when published file refreshes

Dwivedi15 — Wed, 22 Jan 2025 10:55:11 GMT

I think I did it 🙂 - While this data was getting merged in transformation- Date was treated as text and not Date, therefore all the data of Date field after refresh beacuse of BI Service- was overwritten as text ( Although in DAX I converted that filed into date - using normalisation of the filed and created new column) , which then telling that model to treat date as MM/DD/YYYY instead of DD/MM/YYYY.

Re: Measure dissapers when published file refreshes

Dwivedi15 — Wed, 22 Jan 2025 10:55:29 GMT

Re: Measure dissapers when published file refreshes

Dwivedi15 — Wed, 22 Jan 2025 10:55:56 GMT

Re: Measure dissapers when published file refreshes

nilendraFabric — Wed, 22 Jan 2025 10:55:57 GMT

Use ALLEXCEPT to preserve important filters: Instead of using ALL, which removes all filters, try ALLEXCEPT to preserve date filters:

BudgetMeasure_HD =
CALCULATE(
SUM(CombinedTable[Amount]),
ALLEXCEPT(CombinedTable, CombinedTable[Date]),
CombinedTable[Source] = "Budget"
)

Re: Measure dissapers when published file refreshes

Poojara_D12 — Thu, 23 Jan 2025 08:34:25 GMT

Hi Dwivedi15

The issue with the BudgetMeasure_HD disappearing might be due to incorrect data in the Source column, filters, or slicers affecting the measure. Ensure that both "Actual" and "Budget" values are properly populated in the Source column of the combined table. Check that the union of the "Actual" and "Budget" data is working correctly and that the correct rows are being combined. Test the measure in simpler contexts (e.g., without slicers) to rule out filter-related issues. Additionally, clear the cache and refresh the model to address any caching problems.

Re: Measure dissapers when published file refreshes

Dwivedi15 — Thu, 23 Jan 2025 08:38:07 GMT

I think I did it

- While this data was getting merged in transformation- Date was treated as text and not Date, therefore all the data of Date field after refresh beacuse of BI Service- was overwritten as text ( Although in DAX I converted that filed into date - using normalisation of the filed and created new column) , which then telling that model to treat date as MM/DD/YYYY instead of DD/MM/YYYY.

Re: Measure dissapers when published file refreshes

Anonymous — Wed, 05 Feb 2025 02:24:23 GMT

Hi, Dwivedi15

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!