rsDataShapeQueryTranslationError
I'm reaching out because I'm currently facing an issue with a report that works perfectly on both the Local and Dev environments. However, the same copy of the report is not functioning correctly on the UAT and PROD environments, despite the data being identical across all three. The error message I am receiving is as follows: "Error fetching data for this visual. Couldn't retrieve the data for this visual. Please try again later. Please check the technical details for more information. If you contact support, please provide these details. Underlying Error: rsDataShapeQueryTranslationError." To give you some context, I have a measure that connects an import table composite key with a direct table key. If both keys match, I take values from the direct table and display them in the table view. The table has almost 120 columns and numerous rows. Interestingly, when I keep 10 import table columns and 2 direct table columns in the table view, it works fine. However, when I add all the columns, this error occurs. Additionally, I have a button in the report that Action passes a URL (e.g., https:xxx.net/key1, key2, key3, etc.). If the user clicks on the first record from the table, the URL becomes https:xxx.net/key1. Then, if they Ctrl+click on the second record, the URL becomes https:xxx.net/key1, key2, and so on. This works perfectly fine in the Local and Dev servers, but when it comes to UAT and PROD, I immediately get the rsDataShapeQueryTranslationError. Could you please help me understand why this might be happening and how I can resolve this issue? I appreciate any insights or suggestions you may have. Thank you in advance for your assistance. PowerQueryFTW PBI Convert DAX to M Code DAX Measure Query
How to calculate subtotal correctly with hierarchies
Hi I am struggling with the following problem. I have a fact table in which I already receive calculated values per category and subcategory in one table. The totals per category and subcategory do not and should not be equal. Let's assume we have category A, which is divided into 3 subcategories: AA, AB, AC. The entire category A is supposed to have a subtotal of 370, and the subcategory AA is 50, AB 100 and AC 200. The sum of the subcategories is 350, but I would not like to see 350, but 370. I already receive the calculated data from the backend layer: Since I have one column with values that simultaneously contains the sum of the entire category and the subcategory values, the whole thing is duplicated in the sums. How can I approach this differently? This is an example, the real case has a category and a tree with 8 levels of subcategories, so it is very important for me that the solution is calculated correctly.
I need to supress the rows where the column sub-total has no value.
The show items with No data works, if I have not formatted the Amounts in my columns, but once I format it using DAX, this option doesn't work anymore. This one is w/o formatting the amount column This one is after formatting the Amount Columns(Month-Year) My DAX for Visual 2 - What can I add here to suppress rows which have no data in the month columns! Actuals = VAR _selectedHeader = SELECTEDVALUE ('a360_finance fin_PnL_Template2'[Level_1]) VAR _Actuals = FORMAT([Actual Amount],"#,##0.00;(#,##0.00)") VAR _IsLevel2Visible = ISINSCOPE('a360_finance fin_PnL_Template2'[Level_2]) VAR _DisplayDetailCode = SELECTEDVALUE('a360_finance fin_PnL_Template2'[Highlight]) VAR _Revenue = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Sales Revenue"} ) VAR _COGS = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Cost of Products Sold"} ) VAR _CM = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Sales Revenue","Cost of Products Sold"} ) VAR _OCGS = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Other COGS"} ) VAR _SGA = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"SGA"} ) VAR _NOPEXP = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Non Operating"} ) VAR _Results = SWITCH(TRUE(), _IsLevel2Visible = TRUE() && _DisplayDetailCode = 1, Blank(), _selectedHeader = "Revenue", FORMAT(_Revenue, "#,##0.00;(#,##0.00)"), _selectedHeader = "Cost of Goods Sold", FORMAT(_COGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Contribution Margin", FORMAT(_CM,"#,##0.00;(#,##0.00)"), _selectedHeader = "Contribution Margin Percentage", FORMAT((_CM/_Revenue), "#0.0%;(#0.0%)"), _selectedHeader = "Other Cost of Goods Sold", FORMAT(_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Total COGS", FORMAT(_COGS+_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Gross Profit", FORMAT(_Revenue+_COGS+_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Gross Profit Percentage", FORMAT((_Revenue+_COGS+_OCGS)/_Revenue,"#0.0%;(#0.0%)"), _selectedHeader = "Total SGA", FORMAT(_SGA,"#,##0.00;(#,##0.00)"), _selectedHeader = "Total Operating Expenses", FORMAT(_SGA,"#,##0.00;(#,##0.00)"), _selectedHeader = "Operating Income", FORMAT((_Revenue+_COGS+_OCGS+_SGA),"#,##0.00;(#,##0.00)"), _selectedHeader = "Operating Income Percentage", FORMAT((_Revenue+_COGS+_OCGS+_SGA)/_Revenue,"#0.0%;(#0.0%)"), _selectedHeader = "Total Non Operating", FORMAT(_NOPEXP,"#,##0.00;(#,##0.00)"), _selectedHeader = "Net Income", FORMAT((_Revenue+_COGS+_OCGS+_SGA+_NOPEXP),"#,##0.00;(#,##0.00)"), _Actuals //FORMAT(_Actuals, "#,##0.00;(#,##0.00)") ) Return _Results
Need help on DAX
I have two tables - Sales Invoice Lines containing sales transaction and Ledger table containing all financial accounting entries. Both these tables have voucher as common item and hence a relation is created. i need to populate customer's itemized statement in PBI. I have all the details required in sales invoice except GST and PST. In the sales, tax is total whereas tax is split in two lines under GL 271000 and 274000 in the ledger table. when I create a basic measure and/or column for GST and PST, the visual is bringing the GST for all the transaction. but I want ot display the GST and PSt only for the tax impacted line item. my table is as below. in the above, total tax is applicable only on 1 line item. how do I populate the GST and PST only to those lines. my measures are GST = CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID]="271000")) and PST = CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID]="274000")) I have tried columns as GSTAX = IF('TAX Entries'[MAINACCOUNTID]="271000", 'TAX Entries'[TRANSACTIONCURRENCYAMOUNT],0) PSTAX = IF('TAX Entries'[MAINACCOUNTID]="274000", 'TAX Entries'[TRANSACTIONCURRENCYAMOUNT],0). please advise
