Optimize DAX (Sum / Distinct Count) for a percentage result.

[Anonymous]

Hi,

I am trying to take a certain amount and divide it on some unique products. I've looked in the Performance Analyzer and can see that this DAX query is specially heavy.

I have first

Measure A =  CALCULATE(SUM(fact_distribution[frafall]);fact_distribution[Pristypekode] <> "Free")

Which is divided on

Measure B = COUNTROWS(GROUPBY(fact_distribution;fact_distribution[fk_dim_customer];fact_distribution[fk_dim_publication]))

Now from whtat I understand Measure B could be optimized to:

COUNTROWS(SUMMARIZECOLUMNS(fact_distribution[fk_dim_publication];fact_distribution[fk_dim_customer]))

But...this gives an error that says SummarizeColumns() and AddMissingItems() may not be used in this context.

Which from what I understand this is because of the filtering done in the calculate on Measure A?

Any other tips on how I can optimize it? I need to take Measure A and divide on Measure B to get a percentage, and then I also need that same percentage in a Last Year version.

Thanks for any help.

Best,

Ali A

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You can use the following DAX:

COUNTROWS(SUMMARIZE(fact_distribution;fact_distribution[fk_dim_publication];fact_distribution[fk_dim_customer]))

Would yo like to provide some sample data? We can perform more accurate analysis based on it.

[Anonymous]

Hi,

Out of curiosity - what is the difference? I will look it up online, but if you have a practical way of explaining that would be great.

Thanks for the response!

Best,

Ali A