cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

Using dates for filtering grouped table

Hi,

 

Sorry for the basic question, but I have a table that has the following fields:

 

- Transaction Date

- Category

- Subcategory

- Revenue

 

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.

 

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 the date field later for filtering the information.

 

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?

 

Thanks,

 

Ezequiel

5 REPLIES 5
Super User II
Super User II

Hey,

 

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.

 

Maybe you also might consider to create a separate date table and use relationshops in you data model.

 

Hopefully this is what you are looking for and I'm not overlooking the obvious

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

Capture.JPG

 

Any help will be great

 

Ezequiel

Hm,

 

what I described above is basically the magic of Power BI, besides some ohter things 🙂

 

Can you provide a screenshot from the "Relationships" pane something similar like thisimage.png

 

or better provide sample data?

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Sure!. 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.

 

Case NumberProactive or ReactiveStatus ReasonCategorySub CategoryCreated OnRevenue
Case 1ReactiveClosed (Resolved)Category1Subcategory129/03/2018 18:15$                               450.000,00
Case 2ReactiveClosed (Resolved)Category2Subcategory229/03/2018 14:40$                               550.000,00
Case 3ReactiveClosed (Resolved)Category3Subcategory427/03/2018 16:10$                               312.000,00
Case 4ReactiveClosed (Resolved)Category3Subcategory426/03/2018 16:09$                               209.520,00
Case 5ReactiveClosed (Resolved)Category4Subcategory323/03/2018 16:35$                               468.000,00
Case 6ReactiveClosed (Resolved)Category2Subcategory223/03/2018 9:48$                                   75.000,00

Hey,

 

thanks for providing the sample data, I assume that your data model is just one table w/o any separate Calendar table.

 

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:

image.png

 

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:

image.png

 

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:

image.png 

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:

 

image.png

 

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.

 

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.

 

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.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors