DAX too Slow with Filtering
Hi, I have the following measure: Measure = CALCULATE ( COUNT ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[ValidFromDate] < MAX ( 'Date'[Date] ) && 'Table'[ValidToDate] > MAX ( 'Date'[Date] ) ) ) The two tables (Table, Date) are not connected. I use this to see "Active" contracts on a specific date. However when I place it in a visual to see it on a daily basis (last 12 months for example), it is way too slow (I have 12 million rows). Is there a way to optimize this? Much appreciated!
REDO mesures without RELATEDTABLE
Hello, I could not find the answer on youtube, so I ask some help here. I ve got a new dataset to manage which was made not by me. It is very slow and I can't export many visuals to powerpoint directly because it lacks on memory. After investigation - the problem is RELATEDTABLE in DAX syntax - I was able to redo some mesures without relatedtable and it works just fine, but for some mesures I have different result, and old result with RELATEDTABLE is a right one. So my question is there any general tip to get rid of RELATEDTABLE and make my dax more fluent. Example of slow mesure: Base-TotalEng = CALCULATE( [base-F2F]+ [base-Phone]+ [Base-S2S]+ [Base-event]+ [base-FUE]+ [Base-Remote]+ [base-NLADOBE]+ [base-Web] ,RELATEDTABLE('Target') ,RELATEDTABLE('Decision')) Thank you
Measure is slow when using variables
I have been trying to learn more about Optimising Dax using a data sample from sqlbi (DAX Optimizations Examples with Alberto Ferrari) Orders Table has 1.6 million rows of data. I have two identical measures except one does not use variables. The measure with variables takes 14 seconds to execute and the other 0.5 seconds. I am unsure why variables are having an impact on performance. Measure 1 Open Orders = VAR StartPeriod = MIN ( 'Date'[Date] ) VAR EndPeriod = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( FILTER ( Orders, AND ( Orders[Order Date] <= EndPeriod, Orders[Delivery Date] >= StartPeriod ) ) ), ALL ( 'Date' ) ) Measure 2 Open Orders = CALCULATE ( COUNTROWS ( FILTER ( Orders, AND ( Orders[Order Date] <= MAX ( 'Date'[Date] ), Orders[Delivery Date] >= MIN ( 'Date'[Date] ) ) ) ), ALL ( 'Date' ) )
