Advice to optimize performance for a calculation of cumulative %
Hi I'm having a datamodel with a fact table called 'Ordre- og Fakturalinjer', which contains over 15 million rows of order and invoice lines. One of the columns is [Leverandør ID], which is connected to the dimension table called 'Leverandør', which contain 16.000 unique suppliers. I have created these measures to calculate a Akkumuleret Forbrug % (Cumulative %): Total Forbrug = SUM ( 'Ordre- og Fakturalinjer'[Forbrug (DKK)] ) Total Forbrug Rank = IF ( ISINSCOPE ( 'Leverandør'[Leverandør] ), RANKX( ALLSELECTED ( 'Leverandør'[Leverandør] ), [Total Forbrug]) ) Total Forbrug GT% = DIVIDE ( [Total Forbrug], [Forbrug ialt], 0 ) Akkumuleret Forbrug % = divide ( [Akkumuleret Forbrug], [Forbrug ialt], 0 ) Then I use following columns in a table visual: 'Leverandør'[Leverandør] [Total Forbrug] [Total Forbrug Rank] [Total Forbrug GT%] [Akkumuleret Forbrug %] When I trace the performance of the table in DAX Studio it takes almost 167.000 ms. The problem lies in the measure [Akkumuleret Forbrug %]. Any advice to optimize the existing measure or do it in another way to get faster load time for the visual table. Best regards Morten
Slow DAX formula to calculate # Orders Delayed with adjustment
Hi I have a datamodel with orderlines and goods receipt. I have written a formula to calculate number of unique orders delayed (in Danish # Ordre Forsinket) based on two what if parameters. The parameter 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] is used to adjust the delay with X number of days on those orders there are marked as indkøbsbilagsart ZCD or ZLM. The parameter 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] is used to adjust the delay with X number of days on those orders there are different from indkøbsbilagsart ZCD or ZLM. The data model looks like this and there is 1.4 million rows in the table 'Ordrelinjer' and 2.775 suppliers in the table 'Leverandør': Here is the DAX formula: # Ordre Forsinket = VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat], 0 ) VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat], 0 ) RETURN CALCULATE( DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]), FILTER( ADDCOLUMNS( FILTER( 'Ordrelinjer', 'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0 ), "JusteretAfvigelse", 'Ordrelinjer'[Afvigelse i dage] - IF( RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"}, _LeveringstidRegionslagerRabat, _LeveringstidOevrigeRabat ) ), [JusteretAfvigelse] > 0 ) ) The measure is used in a table visual, but it takes around 33 seconds to refresh the table visual. I need a few more measures in the table, so it will just take longer and longer time to refresh the table. Can I write the DAX formula in a more efficient, clean and performance optimized way? Best regards Morten
TotalMTD , TotalYTD is very slow for a specific table
I created following measure in 2 tables, orders and shipments. Both are showing correct value. However measure in orders loads very fast but same measure formula in shipments is extremely slow TOTALMTD( orders[amount], calendar[date]) time : 1524 is very fast but TOTALMTD (ship[amount], calendar[date]) is slow time :6454 calendar is not marked as date table because it is having duplicates. i don't mind, because it is showing correct values
