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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
zfemmer
Helper I
Helper I

Slicer - Filter By 1 of 3 columns

I am trying to be able to give the end-user the ability to filter by 1 of 3 columns, but not any more than that. The columns are MarketSegment, Source, and SourceSummary.

 

I have the following dataset; just a small sample:

DateStatusMarketSegmentSourceSourceSummaryBookedRevenue
09/10/20ConsumedOnline Travel AgentsOnline Travel AgentsOnline Travel Agent759.94
03/22/19ConsumedNational CorporateDirect ReservationNCC313.47
06/28/19ConsumedOnline Travel AgentsOnline Travel AgentsOnline Travel Agent1871.29
10/30/19CancelledDiscount/PromoNational Call CenterNCC151.99
04/15/19CancelledDiscount/PromoDirect ReservationNCC90.24
09/12/19ConsumedDiscount/PromoDirect ReservationNCC582.51
08/10/19ConsumedOnline Travel AgentsOnline Travel AgentsOnline Travel Agent1713.37
10/07/20ConsumedRetailDirect ReservationNCC579.96
05/04/20ConsumedOnline Travel AgentsOnline Travel AgentsOnline Travel Agent94.99
01/24/19ConsumedLocal CorporateDirect ReservationNCC94.99
04/15/19CancelledDiscount/PromoDirect ReservationNCC179.38
07/25/19ConsumedLocal CorporateNational Call CenterNCC380
10/09/19ConsumedOnline Travel AgentsGlobal Dist. SystemGDS787.95

I would like the end-user to be able to select which column they want to filter by:

zfemmer_0-1603465753003.png

Then potentially this would filter another slicer to show those values:

zfemmer_1-1603465825821.png

I have tried a few different data models and I cannot figure out a way to do this.

 

Anyone have any ideas?

 

2 REPLIES 2
wdx223_Daniel
Super User
Super User

@zfemmer using Power Qurey, you can create a table to combine three columns like this

wdx223_Daniel_0-1603696864140.png

then add Category and Content to two slicers respectively, and use the measure like this

TTL = CALCULATE(SUM(SampleTable[BookedRevenue]),FILTER(SampleTable,SampleTable[MarketSegment] IN VALUES(SlicerTable[Content])||SampleTable[Source] IN VALUES(SlicerTable[Content])||SampleTable[SourceSummary] IN VALUES(SlicerTable[Content])))

finally, the result as below

wdx223_Daniel_1-1603697041091.png

 

themistoklis
Community Champion
Community Champion

@zfemmer 

 

This thread will help you.

https://community.powerbi.com/t5/Desktop/Fieldnames-in-slicer-for-1-chart/m-p/1419019#M599357

 

Create a field in a reference table that will have the values 'Market Segment', 'Source' and 'Source Summary'.

 

Add the field above in chiclet slicer visual (if you want). The buttons are shown as in your image

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380756?tab=Overview

 

Then create the measure

 

My variabele col =

VAR Selection =
    SELECTEDVALUE ( 'Ref table'[Variable] )
RETURN

    SWITCH (

        Selection,
        "Market Segment", MAX ( 'Maintable'[Market] ),
        "Source", MAX ( 'Maintable'[Source] ),
        "Source Summary", MAX ( 'Maintable'[SourceSummary] )

    )

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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