Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alex_Hasan
Helper II
Helper II

Filter within TOPN

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? 
Capture.JPG
2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

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]
        )
    )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

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.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

@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]
        )
    )
)

@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. 

Jihwan_Kim
Super User
Super User

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]
        )
    )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

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.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


 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 😊.

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

 

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

Thank you so much

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors