DAX interpreter BUG!!!
The following (code in red) works when run in the Power Bi Desktop version, but as soon as you replace it by the variable name, which contains the same code, Power BI generates an error. In a nutshell, the column [UnreachableSince] is of data type TEXT containing textual representations of DATETIME values and other non-datetime values (I know and there is nothing I can do about this). To intercept the DATETIME values, therefore an IF() is used. It all works fine until you decide to use the variable name in place of the inline code inside the nested IF(). So this OK: DATEDIFF(DATEVALUE([UnreachableSince]) + TIMEVALUE([UnreachableSince]), [ClosedDT], SECOND) But this NOT OK! DATEDIFF(__unreachable_dt, [ClosedDT], SECOND), Code here (sorry but can't add colors when using Insert/Edit code sample from this editor toolbar): ADDCOLUMNS( GROUPBY( ADDCOLUMNS( CALCULATETABLE( SELECTCOLUMNS( ... ), FILTER( ... ) ), "DownTime", IF ( [ErrorCategory] == "Unreachable", VAR __unreachable_dt = DATEVALUE([UnreachableSince]) + TIMEVALUE([UnreachableSince]) VAR __unreachable_dt_in_secs = DATEDIFF(__start_date, __unreachable_dt, SECOND) RETURN IF( __unreachable_dt_in_secs >= 0, DATEDIFF(DATEVALUE([UnreachableSince]) + TIMEVALUE([UnreachableSince]), [ClosedDT], SECOND), BLANK() //DATEDIFF([CreationDT], [ClosedDT], SECOND) ), DATEDIFF([CreationDT], [ClosedDT], SECOND) ) ), [AssetName], "DownTime", SUMX(CURRENTGROUP(), [DownTime]) ), "% up", 1 - ([DownTime] / __in_service_time) ) So am I imagining things, or isn't this a DAX interpreter bug?
All DAX calculations gives an error
Please assist. I tried to do calculations and it gives an error for all of them. I chnages the following DAX code to what i have in my tables: Margins LQ = CALCULATE( [Profit Margins], DATEADD( Dates[Date], -1, QUARTER ) ) QoQ Sales Change = [Total Sales] - [Sales LQ] Sales 1M MA = AVERAGEX( DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -1, MONTH ), [Total Sales] ) and Cummulative Sales = CALCULATE( [Total Sales], FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ))) https://drive.google.com/file/d/1roS73IxPmTE4vFPPRNFkglaH5uuzhXBq/view?usp=sharing
Fix subtotal and total measure evaluated in differente levels of Hierarchy
Good morning I would really appreciate your help with this request: Context: I currently have a matrix within 3 levels in the rows and some measures Rows : Bank name (Category 1) Account Type (Category 2) Account Name ( Catgory 3) What I need is to create a measure who evaluates the account type taking into account there are 2 options, and return an specific calculation different for each one. This last request i just solved it using Measure 1. However, when I try to get the subtotals for Category 1 and the grand total, i just have not figured out how, because it must be sum of the results of evaluating the type of account, I mean the sum of the subtotals I got from Category 2 Table : Account Type cod name 1 COR 2 AHO For identify wich type an account is, I used the code from the table above Measure 1 : VAR COR= [Measure23]-[Measure44]+[Measure45] VAR AHO= [Measure22]-[Measure44]+[Measure45] VAR AT = SUM(Account_Type_code) RETURN SWITCH ( Account_Type, 1,COR,2,AHO,3,"") Here I need to get the right value that would be the sum of 26.498.449.893+7.430.738.545.94 , this per category and grand total. Thanks.lookupvalue sometimes works
Hello all, i have 2 face tables that are not related to one another but are Via customer Dim table: I need to create a calc colum in Fact Table 2 that looks up a column from Fact Table 1 based on some columns that match in BOTH tables: very generic. the problem is that somethimes it works and somthimes it doent (when it totally should!!) and somthines it dosnt work: I know when I get a blank thats because not ALL the condiotns are met but they are. they all meet but I still get a blank any ideas?
Incorrect measure total combining two different tables
Hello everybody, I´m having the following issue: I create a measure that bring either the actual budget execution or my budget to complete.... as I show next: Table 'contabilidad Balance_Prueba' is the actual execution Table Presupuesto is the budget tables 'Ajustes EEFF' and 'Ajustes Forecast' are managerial adjustments. However, when I display then on a table look what it happen: I have actual from Jan to Aug... and forecast from Sept to dec..... if you take a closer look you will see that the total amount is not the sum of the 12 months..... Any thoughts? I would really apreciate any help.
Measure needed for last 6 months weighted average
I have created the weighted average which seems to work desirably but I need the weighted average for last 6 months, below are the measures. Wt avg 2 = CALCULATE(SUMX(RECEIVABLES,RECEIVABLES[Days Diff]*RECEIVABLES[Weights])) Last 6 months Wt Avg2 = VAR enddate = EOMONTH('LAST REFRESHED'[Data Last Refreshed],-1) VAR startdate = EOMONTH(enddate,-7)+1 VAR DateTable = CALCULATETABLE( 'DATE', FILTER('DATE','DATE'[Calendar Date]>= startdate && 'DATE'[Calendar Date]<enddate) ) --------------------------------------------------------- VAR wtavg = CALCULATE( [Wt avg 2],DateTable) RETURN wtavg Although when I see this value by each day then it does not seem to mach but it gives me the correct value for the overall weighted average by every month. So I created another measure which gives the correct value by day wise but the total weighted average days monthwise is not correct. The measure is as; Weighted Avg days = CALCULATE(SUM(RECEIVABLES[Days Diff])*[Weights],VALUES(ACCOUNT[Parent Account])) Last 6 months Wt Avg = VAR enddate = EOMONTH('LAST REFRESHED'[Data Last Refreshed],-1) VAR startdate = EOMONTH(enddate,-6)+1 VAR DateTable = CALCULATETABLE( 'DATE', FILTER('DATE','DATE'[Calendar Date]>= startdate && 'DATE'[Calendar Date]<enddate) ) --------------------------------------------------------- VAR wtavg = CALCULATE( [Weighted Avg days],DateTable) RETURN wtavg In both the cases values do not match to the total for last 6 month weighted average days. Can anyone suggest me what is the mostake I am doing on this 6 month weighted average and what I may need to change
