Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have an excel file as a data source.
I need to create a filter on a column which has priority numbers, and the numbers are under the priority categories.
I need to include the categories only in my filter but the other columns in the table should be filtered based on the numbers under that category.
e.g.
Priority column Description
Category 1
1 Category 1 one
2 Category 1 two
3 Category 1 Three
Category 2
1 Category 2 One
2 Category 2 Two
Category 3
1 Category 3 One
2 Category 3 Two
3 Category 3 Three
4 Category 3 Four
5 Category 3 Five
Category 4
1 Category 4 One
Category 5
1 Category 5 One
2 Category 5 Two
I want to have the filter like this
Filter (Priority Category)
Category 1
Category 2
Category 3
Category 4
Category 5
If I select Category 5 the table should display ‘Category 5 One’ and ‘Category 5 Two’ in the table and so on.
The number of rows under ‘Priority Category’ are dynamic.
I tried to get the index number of the Priority Category and create a column based on the index limit, filled the column with the ‘Priority Category’ titles. But this doesn’t seem to work as I need to fill all the rows with the related ‘Priority Category’ Title.
Any help will be greatly appreciated!!
Thanks!
Solved! Go to Solution.
Thank you vanessafvg!!
I would prefer to do the transform on PBI side.
As I have mentioned above I tried to do that by taking index of the Category rows (which are subheadings in the file). But this didn't work.
Would you please share how did you transform and created a separate column for the priority sequence in PBI?
Many thanks for your help!
The best thing to do is to create a new column and in that column have the value category 1, and then the sequence in a different column like this
see attached.
Proud to be a Super User!
Thanks vanessafvg for your quick reply!
So you mean the 'Priority Sequence' column shoud be created in 'Excel file' (Data source) or in the PBI?
that really depends on your situation, often transforming the data for reporting is required and that is done on a data platform or power bi. I have adjusted your sample data in power bi, should be simple enough to do as well, if you can affect the source obviously thats the most ideal, but in most cases people transform what they have in their data platform. essentially though you should not mix the column you want to filter with another column that just adds a layer of unncessary complexity, its easier to model your data to provide the correct format.
Proud to be a Super User!
Thank you vanessafvg!!
I would prefer to do the transform on PBI side.
As I have mentioned above I tried to do that by taking index of the Category rows (which are subheadings in the file). But this didn't work.
Would you please share how did you transform and created a separate column for the priority sequence in PBI?
Many thanks for your help!
Works like a charm!!!
Thank you very much!!
i included the power bi in my original post, just download it and have a look in power query
but in summary
i duplicated the category column and then remove the sequence and just filled all the values with category 1, or 2, 3 or 4 using a filll down. have you looked at the power bi i attach and look in power query at the steps.
Proud to be a Super User!
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |