Forum Discussion
How to Optimize my Complex dax query
Hi.
I think you need to thin the table down.
Turn off Power BI's auto datetime functionality.
Separate your date related columns into a date table. (Suspect you might need two... what are the LE columns?)
Lots of blogs on date tables but this might get you started:
https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/
You also want to separate related flags out into a separate dimension:
https://m.youtube.com/watch?v=vZndrBBPiQc
https://m.youtube.com/watch?v=vjBprojOCzU
Lastly is MERCH_FORECAST_SNAPSHOT[_Raw Forecast Qty] a measure? If so can you share it's code?
Hi ,
1. There are 3 tables (Calendar table, Merch_Forecast_snapshot table, Article table) and also have relationship.
2. we brought the columns cal date month, Le month index from Calendar table by using related dax function to the Merch forecast_snapshot table.
3. I shared table details the above screen shot those columns only availble in Merch_Forecast_snapshot table.
4. MERCH_FORECAST_SNAPSHOT[_Raw Forecast Qty] is a measure (sum of forecast qty)
5. From article table we are used Major code, Minor code & family code categories.
6. I am showing the data in Matrix table - rows are Le fiscal period, Major code, Minor code & family code. values are 1 mo th, 2 month , 3 month,etc.,(up to 6 months) measures.
7. I think as per the previous conversion i attached dax studio performnace screen shot there you can see 6191 rows which are from Major code, Minor code & family code categories. as my understanding performnce is slow because of 3 columns (6191 rows).
if i remove those 3 rows under matrix visulal only if keep Le fiscal period its getting below 1000 ms.
I think I explain detailed and I don't want to remove those 3 columns from rows in visual . i want to show the data with 4 rows (Le fiscal period, Major code, Minor code & family code.) and is there any way to write/add a dax to that particular column to filter it quickly.
help me from this.
Thanks.