Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
@Anonymous, any thoughts on the work-around I presented?
@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?
@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?
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |