topic Re: Slicer out of measure in DAX Commands and Tips

Slicer out of measure

akfir — Wed, 15 Jan 2020 14:29:46 GMT

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

Re: Slicer out of measure

Jimmy801 — Wed, 15 Jan 2020 21:54:36 GMT

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

Re: Slicer out of measure

Anonymous — Thu, 16 Jan 2020 03:08:14 GMT

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.

Re: Slicer out of measure

akfir — Thu, 16 Jan 2020 10:00:44 GMT

Thanks for your answer, but it does not work out for me. maybe im doing something wrong.

Re: Slicer out of measure

Jimmy801 — Thu, 16 Jan 2020 10:10:54 GMT

Hello akfir

you have to go to your customer-table and add there the calculation of what you want to achieve - like a COUNTROWS of the order-table.

In your visualization you can then add a slicer, using this new column as base.

Hope its clearer now

All the best

Jimmy

Re: Slicer out of measure

akfir — Sun, 19 Jan 2020 19:45:22 GMT

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.

Re: Slicer out of measure

Jimmy801 — Sun, 19 Jan 2020 20:02:29 GMT

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

Re: Slicer out of measure

akfir — Sun, 19 Jan 2020 20:08:08 GMT

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.

Re: Slicer out of measure

Jimmy801 — Sun, 19 Jan 2020 21:28:07 GMT

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

Re: Slicer out of measure

akfir — Mon, 20 Jan 2020 07:02:03 GMT

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.

Re: Slicer out of measure

Jimmy801 — Mon, 20 Jan 2020 07:16:03 GMT

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

Re: Slicer out of measure

Anonymous — Mon, 20 Jan 2020 08:08:12 GMT

Hi akfir

Have you tried my solution?

What error you are getting?

First create table using generateseries.

then use that column in slicers.

Then write below measure on old table.

Filter=sumx(Old_table,if(Old_table[Orders]=selectedvalue(dummy_table[Slicer]),1,0))

Add this measure to your visual level filter and set it to not 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.

Re: Slicer out of measure

akfir — Mon, 20 Jan 2020 12:07:26 GMT

Yes ive tried.
after long time of processing it came with an error says "Visual has exceeded the available resources".
very dissapointing.

Re: Slicer out of measure

Anonymous — Mon, 20 Jan 2020 12:21:26 GMT

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.

Re: Slicer out of measure

akfir — Mon, 20 Jan 2020 12:35:36 GMT

what is PRICE in your model?

Re: Slicer out of measure

Anonymous — Mon, 20 Jan 2020 12:58:40 GMT

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.

Re: Slicer out of measure

akfir — Mon, 20 Jan 2020 13:55:10 GMT

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!!

Re: Slicer out of measure

Anonymous — Mon, 20 Jan 2020 14:04:03 GMT

can you share your PBIX?

Re: Slicer out of measure

akfir — Mon, 20 Jan 2020 14:11:51 GMT

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

Re: Slicer out of measure

Anonymous — Mon, 20 Jan 2020 14:16:55 GMT

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