topic Re: What could be the equivalent DAX of this Power Pivot Search in DAX Commands and Tips

What could be the equivalent DAX of this Power Pivot Search

0Experience — Thu, 07 Mar 2024 19:52:18 GMT

Hello Everyone,

In my power pivot table I can use the below fields to search my result

Filters: Name, Category

Rows: Year

Values: Sum of Rate

Now, what could be the equivalent DAX for this search technique?

I tried

DAX_SUM = Calculate(Sum(Table[Rate]), Allexcept(Table, Tabel[Name], Table[Year], Table[Category]))

From my DAX, if I do not summarize the DAX_SUM then I can see the summation for each year, name, and category is okay. But, if I use the Sum of DAX_SUM it is showing very big summation number.

Re: What could be the equivalent DAX of this Power Pivot Search

lbendlin — Thu, 07 Mar 2024 23:27:57 GMT

ALLEXCEPT removes all other filters. It does not modify the filters on your selected columns.

Not sure what this has to do with search?

Re: What could be the equivalent DAX of this Power Pivot Search

Anonymous — Mon, 25 Mar 2024 09:22:22 GMT

Hi, 0Experience

The method you are currently using and is usually correct to preserve the context of the specified column when summarizing the Rate. However, as Ibendlin said, there is a problem with context, and the problem of incorrect sums when using "DAX_SUM sum" can stem from the way the sum is calculated across the entire dataset without the expected context, and there is an improved approach here

DAX_SUM = SUMX( VALUES(Table[Name]) * VALUES(Table[Year]) * VALUES(Table[Category]), CALCULATE(SUM(Table[Rate])) )

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.