User Profile
Yanant1020
Advocate I
Joined 8 years ago
User Widgets
Contributions
Re: Filter context issues with LINESTX
Hello, has anyone found workarounds for this so slicers work with LINESTX? I am so excited that LINESTX was added and was so disappointed to find out it doesn't work with slicers. I ran a lot of experiments to make sure it wasn't an error in my DAX before coming to post a bug and then I found this bug already exists. LINEST and LINESTX are hugely powerful and bring Power BI's analytical capabilities a giant leap forward. Also if I can be of any help by sharing my code and where it stops working please let me know. After digging into the queries from the performance analyzer it seems to have to do with filters in the SUMMARIZECOLUMNS() that come from the TREATAS() filter table. Not sure why this combination of functions fails to produce a result but that's where I got to. DEFINE MEASURE '0 - Measures'[Test1] = VAR _SelectedMonth = [Selected Calendar Month Index] VAR _LinReg = LINESTX ( CALCULATETABLE ( SUMMARIZE ( 'FACT - Bill To Customer Status', 'DIM - Date'[MMM YYYY], "_MonthIndex", _SelectedMonth - SELECTEDVALUE ( 'DIM - Date'[Calendar Month Index] ) + 35, "_CustomersAcquired", [New Bill To Customers Acquired - 3 Year Lag] ), FILTER ( ALL ( 'DIM - Date' ), AND ( 'DIM - Date'[Calendar Month Index] <= _SelectedMonth + 35, 'DIM - Date'[Calendar Month Index] >= _SelectedMonth ) ) ), [_CustomersAcquired], [_MonthIndex] ) RETURN SELECTCOLUMNS ( _LinReg, "Slope", [Slope1] ) VAR __DS0FilterTable = TREATAS ( { "Mar 2023" }, 'FLOAT - Month'[MMM YYYY] ) VAR __ValueFilterDM3 = FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'DIM - Sales Office'[Sales Office Code - Name], 'DIM - Territory'[Territory Code - Current Owner Full Name], __DS0FilterTable, "New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month", '0 - Measures v2'[New Bill To Customers Acquired - Trailing Rolling 12 Month], "New_Bill_To_Customers_Acquired___Prior_Trailing_Rolling_12_Month", '0 - Measures v2'[New Bill To Customers Acquired - Prior Trailing Rolling 12 Month], "New_Bill_To_Customers_Acquired_____Change___Prior_R12_vs_R12", '0 - Measures v2'[New Bill To Customers Acquired - % Change - Prior R12 vs R12], "New_Bill_To_Customers_Acquired___3_Year_Lag___Least_Squares_Slope", '0 - Measures v2'[New Bill To Customers Acquired - 3 Year Lag - Least Squares Slope], "Test Slope", [Test1], "Amount", IGNORE ( '0 - Measures'[Amount] ) ) ), NOT ( ISBLANK ( [Amount] ) ) ) VAR __DS0Core = SUMMARIZECOLUMNS ( ROLLUPADDISSUBTOTAL ( 'DIM - Sales Office'[Sales Office Code - Name], "IsGrandTotalRowTotal" ), __DS0FilterTable, __ValueFilterDM3, "New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month", '0 - Measures v2'[New Bill To Customers Acquired - Trailing Rolling 12 Month], "New_Bill_To_Customers_Acquired___Prior_Trailing_Rolling_12_Month", '0 - Measures v2'[New Bill To Customers Acquired - Prior Trailing Rolling 12 Month], "New_Bill_To_Customers_Acquired_____Change___Prior_R12_vs_R12", '0 - Measures v2'[New Bill To Customers Acquired - % Change - Prior R12 vs R12], "New_Bill_To_Customers_Acquired___3_Year_Lag___Least_Squares_Slope", '0 - Measures v2'[New Bill To Customers Acquired - 3 Year Lag - Least Squares Slope], "Test Slope", [Test1] ) VAR __DS0PrimaryWindowed = TOPN ( 502, __DS0Core, [IsGrandTotalRowTotal], 0, [New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month], 0, 'DIM - Sales Office'[Sales Office Code - Name], 1 ) VAR __DS0CoreNoInstanceFiltersNoTotals = FILTER ( KEEPFILTERS ( __DS0Core ), [IsGrandTotalRowTotal] = FALSE ) EVALUATE __ValueFilterDM3 //__DS0PrimaryWindowed //ORDER BY // [IsGrandTotalRowTotal] DESC, // [New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month] DESC, // 'DIM - Sales Office'[Sales Office Code - Name] Removing the portions called out in red returns correct results:Re: DAX - count between 2 dates
Ok, fair warning, this is going to be a large measure dump. I went down this path before, isolating each side of the division and seeing if I can't get one of them to run quickly. What you have as PttRv2_1 is what I am calling [Active Customer Count - X Period End] while PttRv2_2 is [Active Customer Count - X Period Start]. Not great names but they work for now. I noticed before that [Active Customer Count - X Period Start] was slower so I started by trying to optimize this one. First, your two measures, plus one tweak I added (countrows instead of distinctcount()) PttRv2_1 = VAR __currDate = MIN('DIM - Date'[Full Date]) RETURN CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"} && __currDate >= 'FACT - Customer Status'[Start Date] && __currDate < 'FACT - Customer Status'[End Date] ) ) PttRv2_1_Countrows = VAR __currDate = MIN('DIM - Date'[Full Date]) RETURN CALCULATE( COUNTROWS('FACT - Customer Status'), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"} && __currDate >= 'FACT - Customer Status'[Start Date] && __currDate < 'FACT - Customer Status'[End Date] ) ) PttRv2_2 = VAR __currDate = MIN('DIM - Date'[Full Date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) RETURN CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"} && __prevDate >= 'FACT - Customer Status'[Start Date] && __prevDate < 'FACT - Customer Status'[End Date] ) ) PttRv2_2_Countrows = VAR __currDate = MIN('DIM - Date'[Full Date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) RETURN CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"} && __prevDate >= 'FACT - Customer Status'[Start Date] && __prevDate < 'FACT - Customer Status'[End Date] ) ) Results: And here are all the versions I tried of [Active Customer Count - X Period Start]. They range in performance from "oh, it's still running" to "something's wrong": Active Customers - X Period Start - v2 = VAR _PeriodYears = [Period Length Value] VAR _PeriodStartDate = DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) ) RETURN CALCULATE ( COUNTROWS ( FILTER ( CALCULATETABLE ( SUMMARIZE ( 'FACT - Customer Status', 'FACT - Customer Status'[Account Number], "Status", CALCULATE ( VALUES ( 'DIM - Status'[Status] ), TOPN ( 1, SUMMARIZE ( 'FACT - Customer Status', 'DIM - Status'[Status], 'DIM - Date'[Full Date] ), 'DIM - Date'[Full Date], DESC ) ) ), DATESBETWEEN ( 'DIM - Date'[Full Date], BLANK (), MIN ( 'DIM - Date'[Full Date] ) ), USERELATIONSHIP ( 'DIM - Date'[Date_Key], 'FACT - Customer Status'[StatusStartDate_Key] ) ), [Status] = "Active" ) ), 'DIM - Date'[Full Date] = _PeriodStartDate ) Active Customers - X Period Start - v3 = VAR _PeriodYears = [Period Length Value] VAR _PeriodStartDate = DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) ) RETURN COUNTROWS( FILTER ( GENERATE ( SUMMARIZE ( 'FACT - Customer Status', 'DIM - Status'[Status], 'FACT - Customer Status'[Start Date], 'FACT - Customer Status'[End Date] ), DATESBETWEEN ( 'DIM - Date'[Full Date], 'FACT - Customer Status'[Start Date], 'FACT - Customer Status'[End Date] ) ), [Full Date] = _PeriodStartDate && [Status] = "Active" ) ) Active Customers - X Period Start - v4 = VAR _PeriodYears = [Period Length Value] VAR _PeriodStartDate = DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) ) RETURN CALCULATE ( COUNTROWS ( FILTER ( CALCULATETABLE ( SUMMARIZE ( 'FACT - Customer Status', 'FACT - Customer Status'[Account Number], "Status", CALCULATE ( VALUES ( 'DIM - Status'[Status] ), TOPN ( 1, SUMMARIZE ( 'FACT - Customer Status', 'DIM - Status'[Status], 'FACT - Customer Status'[Start Date] ), 'FACT - Customer Status'[Start Date], DESC ) ) ), DATESBETWEEN ( 'DIM - Date'[Full Date], BLANK (), MIN ( 'DIM - Date'[Full Date] ) ), USERELATIONSHIP ( 'DIM - Date'[Date_Key], 'FACT - Customer Status'[StatusStartDate_Key] ) ), [Status] = "Active" ) ), 'DIM - Date'[Full Date] = _PeriodStartDate ) Active Customers - X Period Start - v5 = VAR _PeriodYears = [Period Length Value] VAR _PeriodStartDate = DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) ) RETURN CALCULATE ( COUNTROWS ( FILTER ( CALCULATETABLE ( SUMMARIZE ( 'FACT - Customer Status', 'FACT - Customer Status'[Account Number], "Status", CALCULATE ( VALUES ( 'DIM - Status'[Status] ), TOPN ( 1, SUMMARIZE ( 'FACT - Customer Status', 'DIM - Status'[Status], 'FACT - Customer Status'[Start Date] ), 'FACT - Customer Status'[Start Date], DESC ) ) ), 'FACT - Customer Status'[Start Date] < _PeriodStartDate ), [Status] = "Active" ) ) ) Active Customers - X Period Start - v6 = VAR _PeriodYears = [Period Length Value] VAR _PeriodStartDate = DATE(YEAR([Selected Date]) - _PeriodYears, MONTH([Selected Date]), DAY([Selected Date])) RETURN CALCULATE ( COUNTROWS ( 'FACT - Customer Status' ), 'FACT - Customer Status'[Start Date] < _PeriodStartDate, 'FACT - Customer Status'[End Date] > _PeriodStartDate, 'DIM - Status'[Status] = "Active" ) I can update this message with the results of the second set of measures later, but I have a meeting now. Thanks again for all the help.Re: DAX - count between 2 dates
Great point on the calculate, I don't know how I overlooked that. As for the naming convention, you have to give credit where credit is due. I have added three more versions of the original measure you wrote. 1. V1 - same as yesterday 2. V2 - Closer to the way you wrote it originally, but uses an IN to include all the relevant status details 3. V2 (only one detail) - exactly as you wrote it, but it only filters for one status detail so it is not the same calculation as the other measures 4. V3 - same as yesterday but moving the status filter to the calculate. Here are the measures: Pete to the Rescue - v1 = VAR __currDate = MIN('DIM - Date'[Full Date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) VAR __currCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', RELATED('DIM - Status'[Status]) = "Active" && __currDate >= 'FACT - Customer Status'[Start Date] && __currDate < 'FACT - Customer Status'[End Date] ) ) VAR __prevCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', RELATED('DIM - Status'[Status]) = "Active" && __prevDate >= 'FACT - Customer Status'[Start Date] && __prevDate < 'FACT - Customer Status'[End Date] ) ) RETURN DIVIDE(__currCount, __prevCount, 0) Pete to the Rescue - v2 = VAR __currDate = MIN('DIM - Date'[Full Date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) VAR __currCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"} && __currDate >= 'FACT - Customer Status'[Start Date] && __currDate < 'FACT - Customer Status'[End Date] ) ) VAR __prevCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"} && __prevDate >= 'FACT - Customer Status'[Start Date] && __prevDate < 'FACT - Customer Status'[End Date] ) ) RETURN DIVIDE(__currCount, __prevCount, 0) Pete to the Rescue - v2 (only one detail) = VAR __currDate = MIN('DIM - Date'[Full Date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) VAR __currCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] = "Active Customer - New" && __currDate >= 'FACT - Customer Status'[Start Date] && __currDate < 'FACT - Customer Status'[End Date] ) ) VAR __prevCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', 'FACT - Customer Status'[Status] = "Active Customer - New" && __prevDate >= 'FACT - Customer Status'[Start Date] && __prevDate < 'FACT - Customer Status'[End Date] ) ) RETURN DIVIDE(__currCount, __prevCount, 0) Pete to the Rescue - v3 = VAR __currDate = MIN('DIM - Date'[Full Date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) VAR __currCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', __currDate >= 'FACT - Customer Status'[Start Date] && __currDate < 'FACT - Customer Status'[End Date] ), 'DIM - Status'[Status] = "Active" ) VAR __prevCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', __prevDate >= 'FACT - Customer Status'[Start Date] && __prevDate < 'FACT - Customer Status'[End Date] ), 'DIM - Status'[Status] = "Active" ) RETURN DIVIDE(__currCount, __prevCount, 0) And here are the results: If I find the time I will try to run the test in isolation so there isn't any kind of resource contention going on but in reality, the report will have more than this one visual on the page so some resource contention is probably more real to life. I am using import mode. Sticking as close as possible to the measure you originally recommended does result in the fastest processing time, but it is still over a minute. Using the additional fact table I still don't have great performance, but it took 9 seconds in my test. 9 seconds is clearly better but it comes with the addition of a few million record fact table. Having an additional fact table means more system load and potential inconsistency between the fact tables. I would strongly prefer to use the status fact table solely.Re: DAX - count between 2 dates
I had to slightly modify your function because the "status = 3" filter I sent originally isn't a fair representation of the data. In actuality the Status dimension is a hierarchical dimension that relates to the fact table through the child status and I need to filter on the parent status. Here is the function I wrote: Pete to the Rescue = VAR __currDate = MIN('DIM - Date'[Full Date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) VAR __currCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', RELATED('DIM - Status'[Status]) = "Active" && __currDate >= 'FACT - Customer Status'[Start Date] && __currDate < 'FACT - Customer Status'[End Date] ) ) VAR __prevCount = CALCULATE( DISTINCTCOUNT('FACT - Customer Status'[Account Number]), FILTER( 'FACT - Customer Status', RELATED('DIM - Status'[Status]) = "Active" && __prevDate >= 'FACT - Customer Status'[Start Date] && __prevDate < 'FACT - Customer Status'[End Date] ) ) RETURN DIVIDE(__currCount, __prevCount, 0) And here are the results: Your measure has a significant improvement in performance over what I had written, but it is still far too slow (Just over 2 minutes to calculate 1 year). Please let me know if you believe any of the modifications I made to your measure are the cause or if there is another measure design you can think of. Also, here are the results if the new measure is the only one on the page, just to be sure there isn't some kind of cross-dependency occurring. It went down to just over 90 seconds.Re: DAX - count between 2 dates
Thank you again for your assistance on this. I found a workaround I implemented that I would call "less than ideal" because it uses an additional traditiional transaction level fact table for the active customer count calculation. Here are the measures I was originally using that resulted in the poor performance: You can see they closely resemble the recommendation you sent, just broken into pieces. If I throw this final measure into a card, it evaluates very quickly. However, we have a requirement to show this calculation as a daily trend over the last 3 years. When I try to put this measure across a date axis in a line chart, it takes over 30 seconds to return a result and I have it filtered for 1 year, not 3. Edit (some more info): Peformance results for returning 10 day and 365 day trend: Relevant portion of model: Row Counts: DIM - Customers: 552,477 DIM - Status: 6 FACT - Customer Status: 2,036,733 Thoughts: I was reading through Kimball's datawarehouse toolkit yesterday for inspiration and found the aggregating snapshot fact table employed in a similar problem concerning inventory levels over time. They logged for each period (monthly, weekly, daily, whatever is required) what the inventory level was for each part at the end of that period. That could be applied to this case but it would result in the 2 million record status fact table to expand for each day the customer has been in our system. This is definitely possible and I can see where the dax performance would improve, I can also see that SQL would probably handle this expansion fairly easily, it just seems like so much data to store in an in memory database just for performance enhancements.Re: DAX - count between 2 dates
Thank you for your interest in continuing to help me on this. Here is an example of our fact table. As you can see, it relates to multiple dimensions; however, it cannot have an established relationship with the date dimension as the records in this table are represented by date ranges. The measure pattern you supplied before works but even with this table as the fact in a pure star schema, we have performance issues. The primary measure we are experiencing this slow-ness with computes the count of customers in status 3 on the given date, the count of customers in status 3 3 years before the given date (period start), and divides the two. [Given date status 3 count]/[3 years prior status 3 count].Re: CRM Modelling in Power BI
When I initially posted that we have solved this with multiple fact tables I should have clarified that we do NOT connect the fact tables. They are connected through conformed dimensions (i.e. DimQuote connects to FactOrderedQuote and FactQuotedOpportunity). While this pattern works, it results in really poor performance when trying to view the Sales amount for an opportunity. This would be calculated by transversing 6 tables and 5 relationships. DimOpportunity -> FactQuotedOpportunity <- DimQuote -> FactOrderedQuote <- DimOrder -> FactOrderLine. I have not found a way to transform this into a single star, especially considering multiple sets of M:M relationships. For instance, an opportunity can generate multiple quotes and a quote can generate multiple orders. Also one order may result from multiple quotes and one quote may result from multiple opportunities.CRM Modelling in Power BI
This question is largely theoretical so if I don't receive any answers I won't be surprised. I am looking for tips on how to model CRM data in Power BI. Specifically, we use Microsoft CRM and there is a concept of a "Sales Funnel". In my organization, the end goal of a CRM power bi report revolves around reporting the success rate of converting things through the sales funnel. For instance, a user may use the website and this generates a Lead, then a salesperson contacts the user and verifies some information and they become an opportunity, with some luck the opportunity becomes a quote and the quote becomes an order. We want to be able to analyze the conversion rate between each status. In Microsoft CRM, they are able to do this with activities by having a FromEntityId column where the id in the column may reference a record in any number of entities rather than in a traditional foreign key relationship where it would only reference one entity. My team and I have tried a number of different approaches to reporting this in power bi and we have succeeded; however, the models seem bloated and slow in my opinion. Our current approach is modelling each conversion as it's own fact table i.e. FactQuotedOpportunities, FactOrderedQuotes. The issue I've found is that a measure that must jump across multiple fact tables and conformed dimensions is incredibly slow. I know this is a very general modelling question but if anyone has found a good way to model this in Power BI I would love to hear it. Even if it is just a hint at the approach they found. I am sure every company is different in how they use Microsoft CRM or other CRM's. We have something working but I would like to scrap it so I am intentionally not posting more information in hopes of someone giving a tip that completely changes the way I approach this. I have read up on SQL BI and Kimball and consider myself relatively good at power bi modelling but this one stumps me. Thanks
