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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
m_e_1203
Helper II
Helper II

Using a measure as a slicer

Hello,

 

I am trying to do like this post but I could not figure out how to do and need you help.

https://community.powerbi.com/t5/Desktop/Use-Measure-as-Slicer/m-p/1032054#M485879

 
***CY = Current Year / LY = Last Year***  (Fiscal year = Apr - Mar)
Qty CY = CALCULATE([Total Qty], FILTER('Date', 'Date'[Date] >= DATE(2020,4,1) && 'Date'[Date] <= DATE(2021,3,31)))
Qty LY = CALCULATE([Total Qty] , DATEADD('Date'[Date], -1, YEAR))
Qty Difference = [Qty CY] - [Qty LY]

 

I created Sales Quantity Measure like this;

Qty Segment =
IF(ISBLANK([Qty CY]), "No Purchase in 2020",
IF([Qty Difference] < 0.00, "Decrease",
IF([Qty Difference] = 0.00, "Same",
"Increase")))
 
I want to use this text measure as slicer but do not want to create Calculated Column because it did not adjust when I use Month filter. (I could only compare by annual Qty)
 
"Customer Segment" table is created like the thred above. 
How can I connect this to a measure "Qty Segment", so that I can use as a slicer?
 
Thank you for your assitance.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@m_e_1203 , Looking at this seems like you need dynamic segmentation. Please refer if my video can help

https://www.youtube.com/watch?v=CuczXPj0N-k

 

File related to this video attached after signature

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Follow the Blog article here - https://www.daxpatterns.com/dynamic-segmentation/.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

Thank you for sharing the blog. 

Unfortunately, I could not figure out how to complete this...

 

 

 

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Thank you for your reply. Since I cannot share the file due to confidentiality, I will explain here as much as I can.

 

What I would like to do is something like those below.  

 

https://community.powerbi.com/t5/Desktop/Measure-as-Slicer-Workaround/m-p/613366#M292345

https://community.powerbi.com/t5/Desktop/Use-Measure-as-Slicer/m-p/1032054#M485879

 

-----------------------

(1)

Table  (Qty Segment Table) created.

Qty Segment# : Segment

0: No purchase in 2020

1: Decrease

2: Same

3: Increase

-----------------------

(2)

This measure is in "Qty - Measure" folder.

Qty Segment# = IF(ISBLANK([Qty CY]), 0,
IF([Qty Difference] < 0.000, 1,
IF([Qty Difference] = 0.000, 2,
3)))
-----------------------
(3)

Create a measure like this in Qty Segment Table.

Category Selection = IF(SELECTEDVALUE('Qty Segment table'[Qty Segment#]) = 'Qty - Measure'[Qty Segment#], 1, 0)
-----------------------
(4)
It did not filter.
When I select both from the list it shows like this.
0 value does not show 0...... 
 
QtySegment#            | Segment |    QtySegment# |    Qty Segment
(Qty Segment Table) |                     
----------------------------------------------------------------------------
0                             |   No purchase   | 3               | Increase
1                             |  Decrease          | 3               | Increase
2                             |  Same                | 3               | Increase
3                             |  Increase           | 3               | Increase
 
 -----------------------
 
Thank you.

Hi,

I will still need data to work with.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Thank you for your response. 

I was able to follow this instruction https://community.powerbi.com/t5/Desktop/Measure-as-Slicer-Workaround/m-p/613366#M292345.

 

I needed to do #3 after completing #2. 

 

2 create a measure 
Measure = IF([new MaxStatusas Number 3]=SELECTEDVALUE(Table1[slicer]),1,0)

3.add measure in the visual level filter, select "1"
add Table1[slicer] in the slicer

 

Using @amitchandak 's solution and this would solve my issue.  Thanks agai for your assitance.

 

amitchandak
Super User
Super User

@m_e_1203 , Looking at this seems like you need dynamic segmentation. Please refer if my video can help

https://www.youtube.com/watch?v=CuczXPj0N-k

 

File related to this video attached after signature

@amitchandak 

 

Thanky you for the video and PowerBI report.

I was able to create the segment, this was very easy to do and I appretiate the solution.

 

However, when I create a slicer, it won't affect and filter any customers.

I have tried in your report (Retail Bucket Data) on Page 1, and it also did not filter. 

How can I add a slicer to this?

 

Thanks again.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.