Using dates for filtering grouped table

Question

Hi,&nbsp;Sorry for the basic question, but I have a table that has the following fields:&nbsp;- Transaction Date- Category- Subcategory- Revenue&nbsp;I need to biuld a view to show the info grouped by Category, subcategory and revenue. And also be able to filter by transaction date.&nbsp;The solution I found was to group the info from the original table, but on that case I'm removing the Transaction Date to have the info grouped by Category and subcategory, reason why I can't use&nbsp;the date field&nbsp;later for filtering the information.&nbsp;How can I solve that? What Am I missing? How can I include the date back for using it as s filter on the grouped info?&nbsp;Thanks,&nbsp;Ezequiel

tommartens · Answer

Hey,&nbsp;maybe i'm missing some important fact, but why just create a slicer from the columns, whatever you select in the slicer filters / groups the rows in your table.&nbsp;Maybe you also might consider to create a separate date table and use relationshops in you data model.&nbsp;Hopefully this is what you are looking for and I'm not overlooking the obvious&nbsp;RegardsTom

ezequiel · Answer

Hi Tom, thanks for the reply. Maybe I'm missing something, but when I create a slicer on the raw data, and apply a filter (let's say February), the data that will be displayed are all the rows and not filtered. That is why I built the grouped info as shown below, but when I did that I lost the date field

Any help will be great

Ezequiel

tommartens · Answer

Hm,&nbsp;what I described above is basically the magic of Power BI, besides some ohter things :-)&nbsp;Can you provide a screenshot from the "Relationships" pane something similar like this&nbsp;or better provide sample data?&nbsp;RegardsTom

ezequiel · Answer

Sure!.&nbsp;This is the sample of the data. I need to chow the info summarize by Category and subcategory whoing the revenue and being able to filter it by the month the case was created. Let me know if this is clear.&nbsp;Case NumberProactive or ReactiveStatus ReasonCategorySub CategoryCreated OnRevenue Case 1ReactiveClosed (Resolved)Category1Subcategory129/03/2018 18:15$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 450.000,00Case 2ReactiveClosed (Resolved)Category2Subcategory229/03/2018 14:40$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 550.000,00Case 3ReactiveClosed (Resolved)Category3Subcategory427/03/2018 16:10$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 312.000,00Case 4ReactiveClosed (Resolved)Category3Subcategory426/03/2018 16:09$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 209.520,00Case 5ReactiveClosed (Resolved)Category4Subcategory323/03/2018 16:35$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 468.000,00Case 6ReactiveClosed (Resolved)Category2Subcategory223/03/2018 9:48$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75.000,00

tommartens · Answer

Hey,&nbsp;thanks for providing the sample data, I assume that your data model is just one table w/o any separate Calendar table.&nbsp;Here you will find a little pbix file, as the following screenshot shows the data gets filtered by the Created On column, due to the fact your sample data did not contain date values for different months I filtered the data using just a single day. For the filtering I used the "Timeline" visual from the marketplace:&nbsp;Here is a screenshot from the same report where I adjusted one date value from your sample data to be in a different month, now I have 2 months to filter and it still works:&nbsp;Nevertheless, can you please check if your revenue column is a numerical datatype - in no way it has to be "Text".Mark the column, and check the datatype:&nbsp;I had to adjust the values in the revenue column, because it has been inserted into PowerBI as text, due to spaces between the currency symbol and the value. If everything is fine you can recognize that the column can be aggregated by the Sigma sign in front of the column name:&nbsp;&nbsp;You have to change the datatype of the revenue column to a datatype that can be aggregated, but this would be another question and an easy task for Power Query if necessary.&nbsp;There is one thing that catched my eye, the column Created On also has a time information. In most of the cases I create a new column in my table w/o the time part and then I use this column to create a relationship with a separate Calendar table.&nbsp;Wondering if this will help, currently I have no idea why the Power BI magic will not work in your case, or I still do not understand your requirement.&nbsp;RegardsTom

Forum Discussion

Using dates for filtering grouped table

5 Replies

Related content

Filter a Group and use the members as a slicer

Page Level filter for different groups

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

Filter data using slicer