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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
akfir
Helper V
Helper V

Slicer out of measure

i created a measure that calculates number of orders per customer.
i wish to use it as a slicer, so i can choose for example "30", and i will get in the near table - a list of the customers that have 30 orders.

important to mention that number of orders is a limited range of values (0-51)

how can i do that?

thanks in advance,
Amit

24 REPLIES 24
Jimmy801
Community Champion
Community Champion

Hello @akfir 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

Jimmy801
Community Champion
Community Champion

Hello @akfir 

 

a measure returns always a scalar value using the filter context.

To accomplish your need, don't add a measure, but a new column to your customer table and filter this new column with an slicer.

 

Hope this helps

 

Jimmy

Anonymous
Not applicable

Create A dummy table means table which is not in relationship with any other table.

orders_tab=generateseries(0,51)

 

Then create measure on your previous orders measure.

Filter=if(orders=selectedvalue(orders_tab[column]),1,0)

 

then add it to visual level filter and set it to 1.

 

Else simply you can create calculated column and add it into slicer.

 

Or provide self servie by adding measure into visual level filter and user can filter that measure from by his choice.

 

 

Thanks & regards,

Pravin Wattamwar.

https://www.linkedin.com/in/pravin-p-wattamwar/

 

If I resolve your problem mark it as solution and give kudos.

Thanks for your reply.
i have created this dummy table, but yet misunderstood the next step of connecting my 0-51 values column to the original measure of orders.
just to clarify myself - my goal is to have a slicer so i can use a "greater than or equal to" measure of the 0-51 values, for example if i pick greater than 40, then i will get the list of customers which have more than 40 orders.

Jimmy801
Community Champion
Community Champion

Hello @akfir 

 

I've never talked about a dummy table.

Just use your customer-table and add a custom columns with a formula like this COUNTROWS(RELATEDTABLE(yoursalesordertable))

This will add a the number of rows in the sales order table for every customer. For this new row you add a slicer in your visual and everything should be fine

 

Hope this helps

 

Jimmy

it is not the right solution because my customer table has a lot than one row per customer (dates etc.)
thats why countrows does not fit in this case.


Jimmy801
Community Champion
Community Champion

Hello @akfir 

 

so you have no relationship between customer and sales order I suppose. Due to multiple values in both tables.

then create a new table like this on, to create a distinct value for your customer

CustomerDistinct = 
     SUMMARIZE(
        Customer;
        Customer[Customer]
        )

 

Connect this new table to your sales and customer table and add a new column like this

CountSales = COUNTROWS(RELATEDTABLE(Sales))

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Thanks again for your quick reply.

i have only one data table in my model - containing customers and their orders as well, by month. each order has several products.


Anonymous
Not applicable

Table 2 = GENERATESERIES(0,51)
 
 
Measure 2 = SUM('Table'[Price])
 
Filter  = IF([Measure 2]=SELECTEDVALUE('Table 2'[Value]),1,0)Capture.PNG
 
 
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

what is PRICE in your model? 

Anonymous
Not applicable

instead of measure 2 put your measure which you have calculated as count of orders.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

still does not work.

let me share with you my measures (which work fine til now):

Sales = sum(Sales[Sales])
Assortment1 = CALCULATE(DISTINCTCOUNT(Sales[SKU]),FILTER(Sales,Sales[Sales]>0))
Assortment3 = CALCULATE(MeasuresTable[Assortment1],DATESINPERIOD(Sales[Date],max(Sales[Date]),-3,MONTH))

"SKU" is the product and in my model i wish to count the number of products a customer purchased in the last 3 months (ASSORTMENT3).
My output is a simple table visual which shows the list of customers with their ASSORTMENT value (0-51).
I wish to add a slicer that filters the list according to their ASSORTMENT value - for example greater than 40.

im helpless so far!!
Anonymous
Not applicable

can you share your PBIX?

sorry i cant. it has a sensetive data.
but i think what i shared with you suppose to be enough.
as i mentioned before, my rough data contains customers with all their products they purchased on monthly basis.
for example customer x bought 10 of product A on December and 20 of product B on January - each one is a row.

hope it helped...

Anonymous
Not applicable

whatever solution i have provided is working fine.

In our project most of time we followed this technic only.

 

the error you got "out of resources" something.

 

I think may be there is column which is summerizing.

Can you check all integer columns are set to "Don't summerize"?

 

all set as dont summarize.

Capture.PNG

Jimmy801
Community Champion
Community Champion

Hello @akfir 

 

just check out my solution. It applys two summarized tables, connectes them and adds a new column to count the sales order.

link 


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

what about time data in your model?

i appreciate your help!

Jimmy801
Community Champion
Community Champion

Hello @akfir 

 

I've know included a date into my solution. But this date can be included in different way in you filters. Check it out.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Jimmy801
Community Champion
Community Champion

Hello  @akfir 

 

then create a summarized table with only customer and another one with customer and sales order number, connect them all and apply the new column as described

 

Jimmy

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.