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
Power BI Report Builder / DAX-Studio dimension and fakt Filter not working as exptected
Hello, I am wondering why filtering in Dax no working as ecpected. Neither in Dax Studio nor in Power Bi Report Builder with genrated code by the wizard. Datamodel: I use a shared Power-Bi Dataset that is imported from SQL. The model is basically a star-Schema. Fact table in the middle aber dimensions like customer , company, sales or date. Linked by ID and bidirectional. Dimension tables customer company sales date The fact table sales quantity sales value cacaluated Measures sales value in EUR Problem 1: Filtering dimension by an other dimension if I create a dataset with the wizard, where I only display dimension "sales- sales ID" and set a Filter/Parameter "customer - name" to a specific customer (0011260), the salesid are not filtered by the specificy customer. It shows all salesids. Result in report builder: Code: EVALUATE SUMMARIZECOLUMNS('Umsatz Dim Auftrag'[salesid], RSCustomDaxFilter(@UmsatzDimKundeKundennummer,EqualToCondition,[Umsatz Dim Kunde].[customer],String)) Expected Result: Why filtering not working right? my workaround is to add a countrows fact table or any other calculated measure. But I think this cannot be a real solution. Problem 2: Aggregation fact not calculated measures if i create a Dataset with the wizard or event in Dax-Studio Dimension sales - salesid fact quantity count rows because of problem 1 filter: 1 salesid = 395A0000203 Result: Code: /* START QUERY BUILDER */ EVALUATE SUMMARIZECOLUMNS( 'Umsatz Dim Auftrag'[salesid] , 'Umsatz - Fakten'[qty], KEEPFILTERS( TREATAS( {"395A0000203"}, 'Umsatz Dim Auftrag'[salesid] )) , "CountRows-Fact", CALCULATE(COUNTROWS('Umsatz - Fakten')) ) /* END QUERY BUILDER */ Expected result: Code: EVALUATE SUMMARIZECOLUMNS( 'Umsatz Dim Auftrag'[salesid] , KEEPFILTERS( TREATAS( {"395A0000203"}, 'Umsatz Dim Auftrag'[salesid] )) , "SUM_QTY", sum('Umsatz - Fakten'[qty]) , "CountRows-Fact", CALCULATE(COUNTROWS('Umsatz - Fakten')) ) Why do i have to specify the aggregation again in the code? I specified my fact - qty already in Power Bi as summarize by Sum. When i create the fact as measure it surely works. But for this i have to create a simple sum measure for all my measures. greetings Benedikt
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.
