Forum Discussion
Helper IMeasure dissapers when published file refreshes
I have two measures :
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. 🙂
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.
11 Replies
- nilendraFabric
Super User
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
Dwivedi15Hi I tried this :
BudgetMeasure_HD =VAR BudgetAmount =CALCULATE(SUM(CombinedTable[Amount]),CombinedTable[Source] = "Budget")RETURNIF(ISBLANK(BudgetAmount),0,BudgetAmount)Now after this published file gets refreshed- I can see budegt measure is only working for january data . Other all months become zero- Dwivedi15
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.
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.- Dwivedi15
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- Dwivedi15
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.
- Dwivedi15
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.
- Poojara_D12
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.
- Dwivedi15
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.
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!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.
