Slicer out of measure

[akfir]

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

[Jimmy801]

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]

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]

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.

[akfir]

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]

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

[akfir]

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]

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

[akfir]

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]

Table 2 = GENERATESERIES(0,51)

Measure 2 = SUM('Table'[Price])

Filter  = IF([Measure 2]=SELECTEDVALUE('Table 2'[Value]),1,0)

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

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

[akfir]

what is PRICE in your model? 

[Anonymous]

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.

[akfir]

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]

can you share your PBIX?

[akfir]

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]

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"?

[akfir]

all set as dont summarize.

[Jimmy801]

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

[akfir]

what about time data in your model?

i appreciate your help!

[Jimmy801]

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]

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