Distinct count vs countrows summarize/group by
Hi, In my model i got a column that's 50% of DB, that column was imported from SQL and it's the concatenation of column A, B, C, D (they are all integer). I just use that column to get a measure with distinct count. Instead of import that column, i just import the C and D column (A and B were already imported) with much less cardinality and reduce the size of my model. And in my new measure i use: countrows (Groupby A, B, C, D) instead of distinct count, but this one is a killer performance in DAX, we are talking about a table with 80M. I'm not sure if that's confused or not. Any suggestion or work around? Thanks for ur time 🙂
Performance issue with crossfilter function
Could you please guide how to resolve performance issue with my measure in the below case: " Sales Amt = CALCULATE( SUM(sales_table[sales_amt]), CROSSFILTER(slicer_table_sales_at_prod[cust_id], bridge_slicer_tables[cust_id], Both), CROSSFILTER(slicer_table_spend_at_prod[cust_id], bridge_slicer_tables[cust_id], Both) ) " In the sample file(which is just a subset of larger model but exact same relationship nature of the table used), I have use-case of this report to work like: 1. From the first set of slicer (1. Customers that belong to), user can select a condition that will determine dimension values in the report. Say, select those users that belong to a particular Geo and having non-zero SALES for selected products in a particular quarter [and having non-zero SPEND for selected products in a particular quarter]. 2. Now KPI slicers will further slice the to show KPI (SALES) values for the above selected customers for a paricular Quarter [made from selected channel] Please note that one of the use-case should return values in the table like return SALES values for Q1-24(20241) for those customers that have non-zero sales in Q4-23 & non-zero SPEND in Q1-24. But in another case I might ask to return SALES values for Q1-24(20241) for those customers that have non-zero sales in Q4-23 & no condition for SPEND. My alternate approach using SUMMARIZE fails for the later use-case where I don't want to involve table 'slicer_table_spend_at_prod'. I understand that crossfilter only engage the table when a values is selected in the 'Cust with SPEND>0' slicers but SUMMARIZE function is always creating a temp table on 'slicer_table_spend_at_prod' to filter the fact records. Reasoning behind the alternate solution to crossfilter approach: in the final form of our report we have 6 tables of type slicer_table_* nature and the visual is getting very slow. (specially when when we don't engage all the tables used in crossfilter means applying less slicers which is normal when user don't use any slicer at all) But this is the case where we start getting wrong values in the alternate approach using SUMMARIZE (because summarize is filtering by temp tables irrespective of slicers used or not) Could you please suggest a solution here File: https://drive.google.com/drive/folders/1JBrvkUlmloEHVSIs3HUqtEHgAPA9ysnX?usp=sharing Regards, Arjun Greg_Deckler mikeh Mikelytics
Complex Dax Query Help
Hello, I am making my first attempt at a complex Dax query to produce a table of results. I've hit a few bumps a long the way and progress was mostly promising, but I've hit a wall. I'm trying to reproduce a complex SQL stored procedure as a DAX query instead (the users of the query will no longer have sql access and need variable inputs which is why I am pursuing this method). Below is the DAX query I have so far, hopefully the comments make it understandable. I'm happy to hear feedback on whether my approach is wrong, I feel like I'm about 1 step away from the result I'm after but just can't seem to get it across the line. Please feel free to critique any of the query I've written and advise how it could be improved. // DAX Query DEFINE //The following VARs are defined in this way because they originated as What-If Parameters in Power Bi // These are the dynamic user input variables for the query VAR _UpperBound = FILTER( KEEPFILTERS(VALUES('Upper Bound'[Upper Bound])), 'Upper Bound'[Upper Bound] = 559000 ) VAR _LowerBound = FILTER( KEEPFILTERS(VALUES('Lower Bound'[Lower Bound])), 'Lower Bound'[Lower Bound] = 204000 ) VAR _C_ClassReplen = FILTER( KEEPFILTERS( VALUES('C Class Replenishment (days)'[C Class Replenishment (days)]) ), 'C Class Replenishment (days)'[C Class Replenishment (days)] = 7 ) VAR _B_ClassReplen = FILTER( KEEPFILTERS(VALUES('B Class Replenishment'[B Class Replenishment])), 'B Class Replenishment'[B Class Replenishment] = 3 ) VAR _A_ClassReplen = FILTER( KEEPFILTERS(VALUES('A Class Replenishment'[A Class Replenishment])), 'A Class Replenishment'[A Class Replenishment] = 1 ) // Determine which Class each product falls into based on its Annual Use (Fact Table 1) VAR _UsageClass = SUMMARIZECOLUMNS( Products[SalesCode], _UpperBound, // these add in the bound context for use in the measure _LowerBound, "SumDailyUsage", CALCULATE(SUM('Flowline Station Usage'[DailyUsage])), "Class", 'Products'[Class], "SumUsage", CALCULATE(SUM('Annual Usage Value'[Usage])) ) // Based On Class, Determine the replenishment period VAR _ReplenishmentPeriod = SUMMARIZECOLUMNS( Products[SalesCode], _UpperBound, _LowerBound, _UsageClass, _C_ClassReplen, _B_ClassReplen, _A_ClassReplen, "Replen", [Replenishment Period] ) // Calculate the period use for each day of use, looking ahead by the number of replenishment days for the product's class var _PeriodUsage = filter( KEEPFILTERS( SUMMARIZECOLUMNS( Products[SalesCode], 'Flowline Stations'[StationId], Dates[Date], _ReplenishmentPeriod, _UpperBound, _LowerBound, _UsageClass, _C_ClassReplen, _B_ClassReplen, _A_ClassReplen, "DailyUsage", sum('Flowline Station Usage'[DailyUsage]), "PeriodUsage", [Period Usage] ) ), not(ISBLANK([DailyUsage])) ) // Rank each period use for a product at a station Asc and Desc var _RankedUsage = ADDCOLUMNS( '_PeriodUsage', "Rank", RANKX(filter(_PeriodUsage, Products[SalesCode] == EARLIER(Products[SalesCode]) && 'Flowline Stations'[StationId] == EARLIER('Flowline Stations'[StationId])), [PeriodUsage],,asc, dense), "ReverseRank", RANKX(filter(_PeriodUsage, Products[SalesCode] == EARLIER(Products[SalesCode]) && 'Flowline Stations'[StationId] == EARLIER('Flowline Stations'[StationId])), [PeriodUsage],,desc, dense) ) // Find the rank at the 95th percentile of all ranks for each product/station table _ThresholdRank = ADDCOLUMNS( FILTER(_RankedUsage, [ReverseRank] == 1), "ThresholdRank", roundup([Rank]*0.95, 0) ) // **Missing Step** - Create a list of Period Usage for each product/Station where the rank = the 95%ile rank above EVALUATE // ?? TIA for any help & advice
DAX Measure performance - cumulative totals
I'm still relatively new to DAX and I'm trying to create a cumulative total. I have managed to achieve this, but it's incredibly slow to the point of almost being unusable. I know I'm probably making some obvious errors and the slowness is likely down to some kind of iteration issue, so I would really appreciate any assistance. Here is a sample of the data: Each DATE and Model has a single contract value. Then for each level of the hierarchy there are a series of attributes. Eg. Level 1 - System Attributes - Demand, Retail Level 2 - Product Attributes - Construction, Inernal, Red, Interest, Fundamental, Status and so on. I have created a separate cumulative total for each level of the hierarchy and then used a switch in the chart to change between each one. The cumulative total needs to start from the first value in the date range, and it also needs to respond to any filters applied (e.g. DataSeriesKey in the sample data). Essentially the calculation for Cumulative Total is (pseudo code) (If SUM(Position) >0 THEN 1, If SUM(Position) <1 THEN -1) * Contract Value for that Day/Model Here are the DAX measures I have created, this example shows the System cumulative total: This get's the balance for a particular Date/Model and calculates whether the outcome should be 1 or -1 Derived Balance By System = VAR Position = SUMX ( SUMMARIZE ( FactFiscalSummary, FactFiscalSummary[DATE], FactFiscalSummary[Model], "BalanceDiff", SUM ( FactPnLSummary[PositiveBalances_System] ) - SUM ( FactPnLSummary[NegativeBalances_System]) ), VAR _BalanceDiff = [BalanceDiff] RETURN SWITCH(TRUE(), ISBLANK(_BalanceDiff),BLANK(), _BalanceDiff = 0, 0, _BalanceDiff < 0, -1, _BalanceDiff > 0, 1 ) ) RETURN Position This calculates the max contract value for the day Derived Max Contract Value = VAR ContVal = MAXX ( SUMMARIZE ( FactFiscalSummary, FactFiscalSummary[DATE], FactFiscalSummary[Model], "MaxContractVal", MAX ( FactFiscalSummary[ContractValue]) ), [MaxContractVal] ) RETURN ContVal Finally this calculates the cumulative total: CumulTotal = VAR MaxDate = MAX ( 'DimDate'[Date] ) VAR MinDate = MINX (ALLSELECTED(DimDate),DimDate[Date]) VAR Total = SUMX( CALCULATETABLE( ALLSELECTED(DimDate[Date]), DimDate[Date] <= MaxDate && DimDate[Date] >= MinDate ), FactFiscalSummary[Derived Balance By System] * FactFiscalSummary[Derived Max Contract Value] ) RETURN Total As I said, this works and produces the correct values and behaviour, but it's extremely slow and sometimes takes over a minute to calculate. There are about 16 million rows in FactFiscalSummary and depending on how selective the attributes are, this takes much longer. For example, System only has 2 attributes, Product has 6 attributes and takes much longer, etc. Also I have tried putting all of these into the same measure but I don't get the correct results, I can only achieve this by creating three separate measures. Any pointers would be greatly appreciated! Thanks
