Incorrect difference in time between 2 values of same column
Hello, I'm trying to find the difference in time(single column-META_CREATE_DATE) between the two process status - 'New Email' which is the start of the process and 'Complete' which is the end of the process for each 'GUID'. These are the measures I have created to calculate the 'processing time'. New Email Time = VAR newemailtime = MIN('Tbl1'[META_CREATE_DATE]) RETURN CALCULATE(newemailtime,FILTER('Tbl1','Tbl1'[PROCESS_STATUS] = "NEW EMAIL")) Complete Status Time = VAR completetime = MAX('Tbl1'[META_CREATE_DATE]) RETURN CALCULATE(completetime, FILTER('Tbl1','Tbl1'[PROCESS_STATUS] = "COMPLETE")) Processing Time = CONCATENATE(MINUTE([Complete Status Time]-[New Email Time]) & " Min " , SECOND([Complete Status Time]-[New Email Time])& " Sec") The Issue is for some of the GUIDs both 'New Email Time' and 'Complete Status Time' is picking up the same datetime value which is of 'New Email' status. Ex- for GUID highlighted for both status calculations same value is being picked hence showing 0Min0Sec How do I fix this? I'm not sure what's wrong with my DAX! Any advice appreciated.Handling Subtotals for Pre-Calculated (Non-Additive) Measures
I'd like to share a neat pattern that I've discovered recently. Motivation I'm building a report where I'd like to use a small amount of summarized data from another report I've built that has a large and complex data model. In my particular case, I want to pull IRR values at several levels of granularity. The large complex model has measures that allow me to dynamically generate summary tables for whatever configuration of row and column granularities I choose. Here's an example with Category and Subcategory on the rows and Group on the columns. In the new report I'm building, this matrix is exactly what I need. I don't need the flexibility to dynamically choose other granularities to report on and I don't want to bog down my report with all the memory or computational overhead needed to do so. As a result, I've chosen to import this data into my new report by querying the existing complex data model. (I do this by connecting to the complex model dataset as an Analysis Server with DAX query. Refer to this question for a bit more detail.) Query My first thought is to write a simple query like this EVALUATE SUMMARIZECOLUMNS ( ComplexModel[Category], ComplexModel[Subcategory], ComplexModel[Group], "IRR", [IRR] ) The result looks like this: This works fine for all of the non-bold numbers in the matrix above but there's no way to generate the subtotals and grand totals (all the bold numbers in the matrix) since it's only returning the IRR at the lowest level of granularity and these returns cannot be summed or averaged or otherwise aggregated/combined to get the subtotals I'm interested in. My solution to this is to use the handy ROLLUPADDISSUBTOTAL functionality within SUMMARIZECOLUMNS. EVALUATE SUMMARIZECOLUMNS ( ROLLUPADDISSUBTOTAL ( ComplexModel[Category], "IsCategoryRollup", ComplexModel[Subcategory], "IsSubcategoryRollup" ), ROLLUPADDISSUBTOTAL ( ComplexModel[Group], "IsGroupRollup" ), "IRR", [IRR] ) Note: Category and Subcategory are together since the latter is always a subset of the former. The result looks like this Notice the rows with blanks in the first three columns, which correspond to subtotals over those dimensions. This updated query has all of the values we need but how do we use it? Measure Suppose we use a simple measure like this: Simple Measure = SELECTEDVALUE ( Summary[IRR] ) This actually gets us all of the values we need but doesn't display them like I want (i.e. blank value rows and columns instead of subtotal rows and columns. This is what it looks like if we use it in a visual with columns from the Summary table: Savvy DAX folks are probably aware of the function ISINSCOPE I'll use to resolve this. If not, check the related articles linked from its DAX Guide page. My first attempts at this measure looked like some variation of a measure like this: Verbose Measure = IF ( ISINSCOPE ( Summary[Subcategory] ), IF ( ISINSCOPE ( Summary[Group] ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = FALSE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = FALSE () ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = FALSE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = TRUE () ) ), IF ( ISINSCOPE ( Summary[Category] ), IF ( ISINSCOPE ( Summary[Group] ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = FALSE () ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = TRUE () ) ), IF ( ISINSCOPE ( Summary[Group] ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = TRUE (), Summary[IsGroupRollup] = FALSE () ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = TRUE (), Summary[IsGroupRollup] = TRUE () ) ) ) ) Note: You may notice that there are only six scope combinations compared to the total possible 2³ = 8 for the three dimensions, Category, Subcategory, Group. The reason is that I have excluded the cases where Category is rolled up but Subcategory is not. This measure does work but it's ugly and tedious to write. Fortunately, I noticed the relationship between Is-In-Scope and Is-a-Rollup. Whenever a dimension is in scope, select the rows that are not rollups of that dimension. Stated in reverse, select the rollup rows when a dimension is not in scope. This leads to a much more elegant version of the measure: Smarter Measure = VAR IsCategoryRollup = NOT ISINSCOPE ( Summary[Category] ) VAR IsSubcategoryRollup = NOT ISINSCOPE ( Summary[Subcategory] ) VAR IsGroupRollup = NOT ISINSCOPE ( Summary[Group] ) RETURN CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsCategoryRollup] = IsCategoryRollup, Summary[IsSubcategoryRollup] = IsSubcategoryRollup, Summary[IsGroupRollup] = IsGroupRollup ) Using this measure in a matrix visual with Summary[Category], Summary[Subcategory] on the rows and Summary[Group] on the columns now looks just like the original matrix visual within the Complex Model. Neat, eh?
Create calculated column in Live connection
Hello All, I have 2 tables, Table 1 is live connection(Using existing dataset) and Table 2 is import file. The Employee ID present in import file should be shown as "Flag - Internal", and for other records should be shown as "External" or "Blank". We have to bring that Flag column from table 2 to table 1. Using RELATED function we can achieve this but since Table 1 is live connection unable to create Calculate column and Calculate Table. Using relationship between these 2 table if we directly drag and drop the Flag column in a table visual, data will filtered out where Flag = Internal. Flag = External won'tbe appeared in the visual. I believe it should be handled in Measure. Please advice how to achieve this measure.
Return prior value if no value exists for current Month/Year
Hi, I'm having trouble with some dax. This is working as expected for a month with values and returning the prior date. If the current Month/Year does not have a value, it will return the prior month/year. What I am having trouble with is returning the value for that period. For example, May 2023 has no value but we can see that April 2023 has a value. I would like to display the April 2023 value or the previous value until there is a value with a date that is >= selected date. _TEST1 Dated Avg Labor Rate = var _minsel = MAX(DimDate[Date]) var _selected = LASTNONBLANK(FILTER(ALL(DimDate[Date]), DimDate[Date]<=_minsel),[Hmmm]) Return _selected Not seeing April value forward. April Value For reference: Hmmm = sum(VW_SPECIAL_CUSTOMER_LABOR_RATE_UNIONED_VIEWS[LABOR_RATE])
if data not exist in base display blank or 0
Hello guys, i have some column from database in Direct Query Here the result that i want : Id Point email 1 400 [email protected] 2 [email protected] 3 600 [email protected] Here the explanation what i have : For the Id 2 , i want to display empty or 0 for the value "Point" but in my base there is no data for this Id , he has no point then the row dosnt exist, cause of that the row with Id 2 dosnt appear in Power BI , is it possible to have a dax formula to fix that ? (I precise the column point its a Sumx formula). Thank you, if you need more informations dont hesitate please.
How to sync slicer with bookmarks in same page
Hi, I have requirement, to sync all the bookmarks with slicer selection in the same page have 3 bookmarks and 3 slicers/filters, when the user selects any slicer automatically the slicer selection value should get updated to all 3 bookmarks automatically in the same page. may i know how to acheive this and how i sync with bookmarks? Please help asap Thanks
Inventory turnover ratio
Hi guys, I found some posts about calculation of Inventory Turnover ratio (ITR) but unfortunately none of them have solution posted. Therefore I would like to have one post with the final answer to help also other users. Here is the business need - to calculate Inventory Turnover ratio on monthly, quarterly and yearly basis & per product & per warehouse. We want to also calculate ITR for all months/quarters/years so we can visualize the trends. Tables available: 1) Monthly Item Inventory (purpose of this table is to store the inventory figures at the end of each month) with following columns: Date (31.12.2022, 30.11.2022 etc) Product ID Warehouse ID Quantity Value of stock 2) Item Ledger (this table captures all items of Items - purchases, sales, internal transfers) with following columns: Date (date of transation) Transaction Type (Sale, Purchase, Transfer) Product ID Warehouse ID Quantity Price Value of transacion (= Quantity * Price) Calculation of Inventory Turnover ratio - formula for ITR is following: ITR = Cost of Goods sold / Average Inventory Average Inventory = ( Value of stock at start of period + Value of stock at end of period ) / 2 Cost of Goods sold = sum of values of transaction (where transfer type = "Sale") in specific period (see below) Coming back to the business need, we need to calculate Inventory Turonver ratio of several time period: Monthly - in this case we want to calculate ITR for previous calendar month and calculation will look like: Sum of values of transaction (sales) sold from 1.12.2022 to 31.12.2022 Average Inventory = ( Value of stock at 30.11.2022 + Value of stock at 31.12.2022 ) / 2 The same logic applies for month-2, month-3, month-4 etc. Quarterly - in this case we want to calculate ITR for previous calendar quarter (Q4/2022) and calculation will look like: Sum of values of transaction (sales) sold from 1.10.2022 to 31.12.2022 Average Inventory = ( Value of stock at 30.9.2022 + Value of stock at 31.12.2022 ) / 2 The same logic applies for Q3/2022, Q2/2022, Q1/2022, Q4/2021 etc. Yearly - in this case we want to calculate ITR for previous calendar year and calculation will look like: Sum of values of transaction (sales) sold from 1.1.2022 to 31.12.2022 Average Inventory = ( Value of stock at 31.12.2021 + Value of stock at 31.12.2022 ) / 2 The same logic applies for years 2021, 2020 etc Thank you for any inputs. I tried to be as specific as possible but if you have any questions, just let me know. IvanSfindout date difference between two dates and non dates
Hi PBI experts, I have one simple requirment need to findout date differnece between ''Service date'' and ''Term.Date'' 2.But for Emp.No - 19031 i dont have data in "Service Date" Column at that time i need to take data from ''Valid from'' first column data (ex. - emp no - 19031 ,Valid from - 11-04-2019 and term date - 11-04-2022) here i need to find date difference between Valid from and Term.Date) here i need find two dax function could you please suggest. Thanks, Shaik
Creating masses of DAX-Measures in an efiicient way
Hi there, is there a more efficient way to create a lot of measure for comparing values then doing it one-by-one? I have to conditionally format values in a 11 x 4-Matrix visual. For that every Value needs to be compared to a definite Benchmark value (< 11 x 4, some benchmarks can be aggregated) To get the Benchmark measures I have created a pattern for a LOOKUPVALUE() measure to fetch values from the benchmark table General Rule:If (Matrixvalue > Benchmark;RED;"GREEN") or some other result to use in coloring-rule Actually there is a minimum number of Measures to create (only for comparison) of 2 x 11 x 4 = 88 My question: is there a way / a tool to "import" prepared and parameterized measures to a PBI Model to avoid going one-by-one? Any ideas? thanky s for input Thomas
