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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to implement slicer for an Alphanumeric field

I am struggling to achieve the following in Power BI Desktop (I am using DirectQuery mode) - I have a table with a column named ICC_Code which has alphanumeric values in this format:  A1, A2, A3, A4, A5, A6, A7, A8, A9, A0, B1, B2, B3, B4, B5 ......., P7,P8,P9,P0. The requirement is to create a slicer like the numeric one with from and to fields so that it includes all ICC_Codes between the from and to values provided by the user. Suppose the user inputs B3 as From and C4 as To, then the filter should be applied for B3, B4, B5, B6, B7, B8, B9, B0, C1, C2, C3 and C4. Any help is highly appreciated

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

Might be difficult to achieve in DirectQuery mode so you might have to use a Composite Model. Generally you handles these sorts of things with disconnected tables that you use as your slicers. You grab the MAX/MIN value from each of these tables and write your measures such that they do the filtering between these values. You might be able to do it with two slicers that do not filter one another. Grab the MIN and the MAX of the table, which should represent your two "ends" and then write your measures accordingly. 

 

But, sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@Anonymous, any thoughts on the work-around I presented?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@Anonymous

Well, if you want to do this in the report view, it is easy, just select a slicer as your visualization and drag your ICC_Code to it. 

Now, you can select the mode of the slice as "Between". 

Then select a table or whatever you want to it to be filtered by this slicer, and simply select the values that you want in the slicer, and the report view will filter it. 

For example, in my dataset, I have a Customer Number field, I have put it as a slicer as you see it below, and then I created a table by simply drag and drop the fields that I want, and whenever I select the values in the slicer the values in the table will be changed.

 

 

Did it help?rKs9E.png 

Anonymous
Not applicable

@Anonymous yes I'm aware of this functionality but this will only work for numeric columns. Mine is an alphanumeric one with the pattern A1, A2, A3, A4, A5.... A9, A0, B1, B2, B3........, B9, B0, C1....... So the only 2 options i see in the slicer are dropdown and list. I understand there's no direct way we can do this. I am looking for a workaround. 

@Anonymous, any thoughts on the work-around I presented?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

Might be difficult to achieve in DirectQuery mode so you might have to use a Composite Model. Generally you handles these sorts of things with disconnected tables that you use as your slicers. You grab the MAX/MIN value from each of these tables and write your measures such that they do the filtering between these values. You might be able to do it with two slicers that do not filter one another. Grab the MIN and the MAX of the table, which should represent your two "ends" and then write your measures accordingly. 

 

But, sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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