topic Re: Filter within TOPN in DAX Commands and Tips

Filter within TOPN

Alex_Hasan — Wed, 01 Jun 2022 18:17:26 GMT

Hi, I want to write a measure that gives the top 10 parent customers' name. The measure below only shows the name of top 10 customers regardless of parent or not parent.

CALCULATE(

[Total Sales],

KEEPFILTERS(TOPN(10, ALL(DimCustomer[FirstName]), [Total Sales])))

this is the column of DimCustomer table that represents whether a customer is a parent or not. How to include this column inside that measure?

Re: Filter within TOPN

Jihwan_Kim — Wed, 01 Jun 2022 18:34:13 GMT

Hi,

Could you please check if the below measure works?

new measure = CALCULATE ( [Total Sales], KEEPFILTERS ( TOPN ( 10, SUMMARIZE ( FILTER ( SUMMARIZE ( ALL ( DimCustomer ), DimCustomer[FirstName], DimCustomer[Is parent] ), DimCustomer[Is parent] = "Parent" ), DimCustomer[FirstName] ), [Total Sales] ) ) )

Re: Filter within TOPN

tamerj1 — Wed, 01 Jun 2022 18:32:16 GMT

Alex_Hasan

For some reason I've never succeeded to answer any of your questions before 😅

Maybe this time. Please try

top 10 parent customers' name = CALCULATE ( [Total Sales], KEEPFILTERS ( TOPN ( 10, FILTER ( ALL ( DimCustomer[FirstName] ), CALCULATE ( VALUES ( DimCustomer[Is parent] ) ) = "Parent" ), [Total Sales] ) ) )

Re: Filter within TOPN

Alex_Hasan — Wed, 01 Jun 2022 18:48:44 GMT

tamerj1 ha ha ha..... Most probably I couldn't make you understand my problem. But your code is not working this time. Maybe you forgot to add something in the measure. Here Jihwan Kim provided the solution and it worked.

Re: Filter within TOPN

Alex_Hasan — Wed, 01 Jun 2022 18:49:29 GMT

Thank you so much

Re: Filter within TOPN

Alex_Hasan — Thu, 02 Jun 2022 04:24:15 GMT

Hi Jihwan_Kim , I've found a problem in the measure. It's actually not showing the top 10 parents or top 10 not parents, rather top 10 customers. I've altered the code to see the effect but both are the same. On the other hand, Summerize function has a bug and Alberto Ferrari of SQLBI told to avoid this function.

Re: Filter within TOPN

Jihwan_Kim — Thu, 02 Jun 2022 04:54:21 GMT

Hi,

Thank you for your message, and please share your sample pbix file's link together with how the expected outcome looks like, and then I can try to look into it to come up with a more accurate solution.

Re: Filter within TOPN

Alex_Hasan — Thu, 02 Jun 2022 05:20:12 GMT

Hi, See the TOPN page. I've put three measures side by side.

https://1drv.ms/u/s!AtNgJBWI0wXOdOdgTjqFwCG_k-c?e=SEHfba

Re: Filter within TOPN

Alex_Hasan — Thu, 02 Jun 2022 18:50:25 GMT

Hi Jihwan_Kim , have you been able to solve this? I am waiting for your solution.

Re: Filter within TOPN

Jihwan_Kim — Fri, 03 Jun 2022 03:13:14 GMT

Hi,

After I see your pbix file, I am confused and I am not sure what you want to visualize.

Do you want to rank total sales by First name? For example, I see that there are so many "Aaron"s and some of them are parent and some of them are Not parent.

I think you can put a correct column in the visualization to check top sales.

I did not check whether the measures still work, but I inside the TOPN function in each measure, including TOP sales measure, please select the correct column, or you can just select the whole table if there are other columns involved such as sorting purpose, for instance.

Thanks.

Re: Filter within TOPN

Alex_Hasan — Fri, 03 Jun 2022 04:27:20 GMT

Now It worked. I didn't notice the fact you just mentioned here. I've made a new column with "Full Name" and instead of choosing first name, I selected the Full Name and it worked. Thank you so much 😊.