The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I want to convert one measye "Sales Type Indicator " Into Slicer. and used below steps , but its not working. Note:- Monthly Avg. SKU No., Current Month Sales and Sales type Indicator all are measures.
Power BI does not allow direct use of measures in slicers. However, you can simulate slicer behavior using a disconnected table. This document outlines the steps to create a slicer for the 'Sales Performance Category' measure using a disconnected table approach.
Go to 'Modeling' > 'New Table' and enter the following DAX expression:
SalesPerformanceCategorySlicer =
DATATABLE(
"Category", STRING,
{
{"Growth"},
{"De-Growth"},
{"Product Loss"},
{"No Data"}
}
)
Create a measure to capture the selected slicer value:
SelectedCategory = SELECTEDVALUE(SalesPerformanceCategorySlicer[Category])
Use the following DAX to define your measure:
Sales Performance Category =
VAR CurrentMonthSales = SUM('Table1'[Current Month Sales])
VAR AvgMonthlySales = AVERAGE('Table1'[Monthly Average SKU No.])
RETURN
SWITCH(
TRUE(),
CurrentMonthSales > AvgMonthlySales, "Growth",
CurrentMonthSales < AvgMonthlySales && CurrentMonthSales > 0, "De-Growth",
CurrentMonthSales = 0, "Product Loss",
"No Data"
)
Create a measure to filter visuals based on the slicer selection:
ShowData =
VAR Category = [Sales Performance Category]
VAR Selected = [SelectedCategory]
RETURN
IF(Category = Selected || ISBLANK(Selected), 1, 0)
Solved! Go to Solution.
Hi @AartiD ,
Thank you for sharing your scenario. I have reproduced the requirement using sample data and can confirm that everything is functioning as expected
1. The Sales Type Indicator measure is categorizing the SKUs correctly.
2. The disconnected slicer is filtering the visuals based on the user's selection.
3. No calculated columns were required as everything is managed through DAX measures.
FYI:
I have attached a PBIX file with the complete setup for your reference. Please let me know if you would like a walkthrough or any further adjustments.
Best regards,
Yugandhar.
Hi @AartiD ,
Thank you for sharing your scenario. I have reproduced the requirement using sample data and can confirm that everything is functioning as expected
1. The Sales Type Indicator measure is categorizing the SKUs correctly.
2. The disconnected slicer is filtering the visuals based on the user's selection.
3. No calculated columns were required as everything is managed through DAX measures.
FYI:
I have attached a PBIX file with the complete setup for your reference. Please let me know if you would like a walkthrough or any further adjustments.
Best regards,
Yugandhar.
Superb, this is perfect and working. thank you so much.
Please clarify what you mean when you say
I want to convert one measure "Sales Type Indicator " Into Slicer
Do you mean that you want to give options to the user to select an item from a slicer and show the corresponding measure into the graph?
If that is the case, please use Field Parameters, (create a new one from Modeling - Parameters, Fields - give it a name, include the measures you want the user to be able to select from the slicer, leave the "Addslicer to current page" flagged, go ahead with clickign "Create" and then include the colum name of the Fiels into the Values section of the graph)
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI