Visualisation (Slicer and Button Slicer)
Hi everyone, I hope you all have a nice day. I want to ask (pictures attached), I gave an example of me using visualisation of "Button Slicer (Top)" and "Slicer (Bottom)" in the picture, I have a table where I have Reporting Date column then I make custom column for the quarters and months. The "Year" in the slicer is from Reporting Date hieararchy (Year), but the quarter and month in the slicer, they both are custom column that I made from the Reporting Date column (so they are not the hieararchy of "Reporting Date"). My questions (I don't mind if you answer with "Slicer" visualisation or "Button Slicer" visualisation): In Picture 1, If I select Q1 and it turns grey, how do the visualisation slicer for months turns black (for Jan, Feb, Mar) without me selecting the Quarter slicer? In Picture 2, If I select any month (for example: Aug) and it turns black, how do the visualisation slicer for quarter turns grey (Q2) without me selecting the Quarter slicer? In Picture 3, If I select Year "2025" and it turns black, how do the visualisation slicer for quarter and months also turns black without me selecting Quarter and Month slicer? Please kindly help me answer my questions. Thank you in advance. God bless. 😊🙏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.
Unable to filter data via parameter using PowerQuery(Snowflake as data source) in PBI Report Builder
Hi, We are using Power BI Report builder for developing the Paginated report and have connected Snowflake as data source by PowerQuery and mapped required parameters to the report query that were created under the report parameter section. In Report query, we have created parameters and have bind them. While viewing the report, data is not getting filtered as per the parameters value selected. Attaching the screenshots for the reference. In the below screens, you can see that the filter is not working as expected. Please suggest what could be the next steps or some workaround for the same. @PowerBIReportBuilder Snowflake @PowerQuery @Paramaters Thanks, Sonali Sahu.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. IvanS
