Forum Discussion

Srikantht's avatar
Srikantht
Helper II
4 years ago

How to Optimize my Complex dax query

Hi Guys, I really appriciate if some one gives solution for the below dax query..    The below dax query need to be optimized. How?  this query running 2400 ms. So I want to optimize it.    1 Mo...
Need Help
bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

Can you send a picture of your data model? It looks like you're running a single table and so everything requires a full scan of the table. Can you send a list of all columns?

If we can separate off dimensions into a star schema I suspect we can improve efficiency.

  • Srikantht's avatar
    Srikantht
    Helper II
    4 years ago

    Hi ,

     

    here is the Columns,

     

     

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      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?

       

      • Srikantht's avatar
        Srikantht
        Helper II
        4 years ago

        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.