DAX measure running slow

[Anonymous]

Hi All,

My DAX meaure is working fine on smaller data set but for large volume its taking time , need some help in modifying & fixing performance issue.

Data granularity : order/product/line

Delivered is flag that indicates if ordered item is delivered or not .Order can have multiple item and item can multiple lines .

Measures Logic:

Success = sum of Delivered order/item

-----------------------------------------------
This is at item level that mean if there are multipe lines for an item in an order it will be counted as 1 thats why total sum is 6 instead of 7

Success :=
CALCULATE (
SUMX (
SUMMARIZE ( Example, Example[Order], 'Example'[Product] ),
CALCULATE ( MAX ( Example[Delivered] ) )
),
ALL ( Example ),
VALUES ( Example[Order] )
)


ProductCount = Total item counts within an order

-----------------------------------------------------------
ProductPerOrder: =
CALCULATE
(
COUNTROWS(VALUES(Example[Product]))
,ALL(Example)
,VALUES(Example[Order])
)

Success% = Sum of Success per order / total item counts per order

--------------------------------------------------------------------------

      Success% := Success / ProductCount

Sample Data

thanks

[mahoneypat · Accepted solution]

Please try this expression to see if it is more performant (and if it gets expected result).

Success =
COUNTROWS (
SUMMARIZE (
FILTER ( Example, Example[Delivered] = 1 ),
Example[Order],
Example[Product] 
)
)

Add Example[Line] into the Summarize too, if needed

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi,

Thanks for providing alternate approach for the measure,Its working but performance for this measures is almost same as what i was using .Upon testing more it seems the issue is with measure  ProductPerOrder  its not performing as other measure and slowing down the report.

Is there any alternate approach that you can suggest to count no of product within an order  other than what i am using.

I am not using ALLEXCEPT for this as ALLEXCEPT only works when Product data is in same table but if Product data is coming from other table its removing filters on colum specified. In my real dataset  product is coming from product dimension.

My calculation for ProducstPerORder

ProductsPerOrder =
CALCULATE
(
COUNTROWS(VALUES(Example[Product]))
,ALL(Example)
,VALUES(Example[Order])
)

Thanks

[parry2k]

@Anonymous there are too many unknowns and your model is not clear, it is hard to find out what is going on? If you put together a sample pbix file, it will be very helpful. You cannot pinpoint performance issues by looking at DAX there could be other factors. If you share pbix with expected result, I will dig into it. I have worked with very complex measures and many large datasets and able to figure out where the problem is, and this particular calculation looks straight forward so there is something else going on.

[Anonymous]

Hi ,

Apologize for confusion below is onedrive link for sample data of how my real data model looks like.

I have created the measures that i want and also created one measure   for ProductPerOrderAllEXCEPT   and once you drag that measure to model you will notice what i meant by its not keeping the filters.

I modfied the calculation of ProductPerOrders( taking some help from this post ) and so far its performing really good.

Please let me know if you find any issues with the measures in the pbix or think it can be modified to improve performace.

ProductsPerOrder =

CALCULATE (

COUNTROWS (

SUMMARIZE ( OrderDetails, OrderDetails[Order], 'Product'[Product] )

),

ALL ( OrderDetails ),

VALUES ( OrderDetails[Order] )

)

Link for sample pbix.

https://hawkiit-my.sharepoint.com/:u:/g/personal/sadhika6_hawk_iit_edu/EZ4y-fEgb1VLqZwWAFsq4msBkGT5a...

Really appreciate you looking into this and helping out and gained s

Regards

[mahoneypat]

Glad to hear it is working better.  I downloaded your pbix and see it only has a few rows.  I thought we might see a bigger dataset for optimizing the measures.  From your different posts, it seems like both measures are working better.  Do you still have an issue?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@Anonymous , check if this gives you same and fater result

Averagex(summarize(summarize(Example,Example[Order], 'Example'[Product],"_1",MAX ( Example[Delivered] )),[Order],[_1],"_2",count([Product])),Sum([_1]),Sum([_2]))

[parry2k]

@Anonymous I think you are over complicating the measures, try following:

Success :=
CALCULATE (
SUMX (
SUMMARIZE ( Example, Example[Order], 'Example'[Product] ),
CALCULATE ( SUM( Example[Delivered] ) )
)



ProductPerOrder: =
CALCULATE
(
DITINCTCOUNT(Example[Product]),
,ALLEXCEPT(Example, Example[Order])
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Hi ,

Thanks for providing solution  i had the same calculation that you provided before but both the cases it was not working.

Success :  If there are multiple lines per item then sum will sum success values at item level 

                but i want to count the success only 1 time so i used MAX

ProductPerCount :  This works fine with the sample  but i guess i missed to mentioned one more thing  sorry about that.

                               This is fact tabel  but actual data model has a product dimension and ALLEXCEPT is not working 

                               if we pull  product information from product dimension table  that's why i used VALUES .

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

Is there any other approach to achive this ?

Regards