User Profile
Simeon
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Power BI DAX Measure - Summarize Across multiple Dimensions and Facts.
Hi everyone, hope you're well. I have a general question regarding the best way to write a Power BI DAX mesure in a specific set of cases. I've been looking around online and can't quite find a solution to my problem. Say that we have a hypothetical Star schema as per the image below with 3 dimensions for a hypothetical retail company. The retail company sells Items (SKUs) through its Stores (Locations). Therefore we have 3 dimensions: Date, SKU and Location. We also have 2 facts: Daily Opening Inventory (Fact_Inventory), and Daily Minimum Required Stock (Fact_Inventory_Req). All relationships are One-Directional, One-to-Many. I want to write a measure which counts the number of occurences where an Item in a Store had an Inventory Level below the Required Safety Stock (each day). I am struggling to find the best and most optimal way to write such a measure. At first glance, I would use SUMMARIZECOLUMNS, however it turns out that this cannot be used in Power BI measures. Therefore, currently my solution is to write something as per below: Measure = VAR SummarizedTable = SUMMARIZE( CROSSJOIN( VALUES( 'Dim_Date'[Date] ) ,VALUES( 'Dim_SKU'[SKU Key] ) ,VALUES( 'Dim_Location'[Location Key] ) ) ,"Opening Inventory", SUM( 'Fact_Inventory'[Inventory_Qty] ) ,"Required Inventory", SUM( 'Fact_Inventory_Req'[Inventory_Qty_Req] ) ) VAR Result = COUNTROWS( FILTER( SummarizedTable ,[Opening Inventory] > [Required Inventory] ) ) RETURN Result This measure is accurate, but it's too slow, especially if we have a large number of items and stores, and we're evaluating the measure over a long period like a year. One workaround I've found is to combine the two fact tables into one fact table, and use an interative function over the single fact table, but in other similar cases I have fact tables in different grains. Also, I want to avoid using workarounds as a crutch for poor DAX skills. Another thing I've done is add a boolean calculated column to the Dim_Item table which returns TRUE if a SKU exists in the Fact_Inventory_Req table, and added this as a page filter (= TRUE), so the measure only evaluates for SKUs in the Fact_Inventory_Req fact table, but it's still slow. My main questions are: Is there a more optimal way of writing such a masure? Is perhaps my star schema not correct? Will SUMMARIZECOLUMNS become available for measures in the future? Many thanks for your help! 🙂Re: PowerQuery - Validating queries when saving is taking excessively long time
I have the same issue. I have a few queries that combine a large number of excel files located in the same SharePoint folder. If I only leave a few files in that folder, the save is much quicker and once saved I can move the files back into the folder and refresh, so it must have something to do with the amount of data that needs processing, especially if you have steps such as Group, etc. Not sure there's a simple way to do this if your data is in any sort of database. +1 Definitely need the Skip Option! Splitting the dataflow into multiple dataflows is also a solution. One for Pre-Stage, and one for transformation works well for me.
