Forum Discussion

CRyan1984's avatar
CRyan1984
Frequent Visitor
5 years ago

Measures and Efficiency Advice

My question is around the efficiency of measures in Power BI.  I have a large table and several measures that have similar components.


for example, I have 12 index measures similar to the below:

Index_1 = 
DIVIDE(
    DIVIDE( --item segment sales
        CALCULATE(sum('item_tbl'[NET_SALES]),'item_tbl'[Segment]=1),
        CALCULATE(sum('item_tbl'[NET_SALES]),'item_tbl'[Segment]<>0)
        ),
    DIVIDE( --Total segment sales
        CALCULATE(sum('Total_tbl'[NET_SALES]),'Total_tbl'[Segment]=1),
        CALCULATE(sum('Total_tbl'[NET_SALES]),'Total_tbl'[Segment]<>0)
        )
)

 

Index_2 = 
DIVIDE(
    DIVIDE( --item segment sales
        CALCULATE(sum('item_tbl'[NET_SALES]),'item_tbl'[Segment]=2),
        CALCULATE(sum('item_tbl'[NET_SALES]),'item_tbl'[Segment]<>0)
        ),
    DIVIDE( --Total segment sales
        CALCULATE(sum('Total_tbl'[NET_SALES]),'Total_tbl'[Segment]=2),
        CALCULATE(sum('Total_tbl'[NET_SALES]),'Total_tbl'[Segment]<>0)
        )
)

 

Is there a more efficient way to write these similar measures so that I consume less resources in Power BI?  The visual table currently takes about 15 seconds to load and I am hoping to significantly reduce that time.

Would it make a difference if I took the denominator that is repeated in all of the index measures and turned it into it's own measure and then referred to the new denomitator measure in the index measures?

 

Thank you in advance for any advice

 

Need Help

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    The use of a separate measure you reference won't impact the performance.  I suspect your issue is more likely related to the separate tables that seem to have similar columns.  The fact that you need 12 similar measures to get your result suggest your model is not optimal.  Can you share more about your tables and relationships?

     

    Pat

     

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      Not totally clear on your model/data but it seems like you could merge those two tables on the column you are using for the relationship.  In any case, a more efficient would be to leverage the relationship to propagate the filter from one table to the other (two CALCULATES instead of four).

       

      Pat

       

      • CRyan1984's avatar
        CRyan1984
        Frequent Visitor
        5 years ago

        could you give me an example of propagating the filter from one table to another in regards to a measure?  I don't think I am familiar with this.

    • CRyan1984's avatar
      CRyan1984
      Frequent Visitor
      5 years ago

      Thanks for your reply,

       

      My item table contains sales metrics by item. an example would be (i tried to post as a table but it would not work)

      1. item code 
      2. region 
      3. sub region
      4. segment1
      5. segment2
      6. net sales
      7. unit qty
      8. other sales metrics


      The total sales table is essentially the same but the information is aggregated at the total for that region, sub region, segment 1 and segment 2 combination.  I should also mention that total sales has more sales than the summation of the first table.  The item table only contains the items that I want to report on.

       

      I have the tables joined in power bi using a key combination of the 4 region, sub region, segment 1 and segment 2 columns

       

      This leads me to ask, would it be best to just combine the two tables into one and then add extra criteria to the measures to pull the right rows?