Report With Specific Filters

[petersi]

I would like to know how to build a report that uses UI controls to select filtering options. I'm lookig specifically for Drop Down Selections and a Date Range Selection.

As well I need to know how to incorporate on the same report and page -- retrieval of data that will be groupued on a date part (like day/month/year)? Sepcifically how can you have a dataset that can be filtered uising this type of grouping (yes my datas  source is a SQL DB). And how would you submit the filters to the data set? 

[MFelix]

Hi @petersi,

The options you are refering are a part of the slicer visual that PBI offers when.you add a slicer you can choose from list, drop down, ... depending on the type.of data.

Also when you add a date field in the slicer / visual you can choose from a hierarchy mode (year, quarter, month, day) and date value (all dates are presented). The visuals in PBI work as filters in your reports so when you select different values in a slicer or a table or.graph your data in the report is filtered and calculated accordingly.

Regards,
MFelix

[petersi]

I need these filters to be dynamically selectable. I.E. -- the user must be able to selet the filter. I AM NOT looking to configure static filters on the report which are absolutely unusable.

[MFelix]

The filters are dinamic and users can choose then is one of the basic features of PBI.

[petersi]

How would a filtering on a dart part be done. I.E. if I have a data set that has  a date field -- and I want to allow the user to be able to specify how to group based the date field. For example

Col1 Col2 Start Time

a     b       c

d    e        f

and I want the user to be able to specify the results will be grouped on Start Time -- possilby by day, month, year. The grouping can be done in SQL like the following:

group by Col1,Col2, datepart(year, Start Time), datepart(month, Start Time)

I would like to know how to do this in Power BI where the user could select if they want to group by year or month or day.

[MFelix]

Hi @petersi,

This can be way in a several different ways, you can have a hierarchy in your table, you can do a measure to make counts, you can make new columns. Did you already tried to play around with PBI and you data to simulate what you need? Can you give a little more detail what you want to achieve with your example?

Regards.

MFelix

[petersi]

I need someone who is familiar with data base concepts. I'm not going to modify anything in my TABLE. It has everything needed. I just need to know how a dynamic grouping can be done on the associated sataset in Power BI. My exampe I gave clearly indicates what I'm trying to do. Please refer to it and understand what it means to group data. 

[MFelix]

Hi @petersi,

Just trying to help and when I ask for additional information is to give you the correct response to your problem I'm familiared with data base concepts, and when you use the group in a data base you use normally an aggregator of some kind (sum, count, distinct count, whatever) and the other columns you will return the dataset, sometimes when you do this without the correct aggregator you will get duplicated result in some columns (this is my experience with grouping in datasets).

As I said you can do the grouping in PBI in different ways, you can do it in measures, in new columns, in new tables, even in the advance query editor if needed, In your example you make an aggregation but then the result is a, b,c,d,e,f so sorry for not understading what you want to achieve, maybe someone else can help.

Apoligize for making you loose your time.

regards,

MFelix

[petersi]

Then how do you do it in Power BI where a filter can be placed so that the grouping is initiated in a specific way.

I.E. -- is there a way to configure a filter with three options

day

month

year

-- and when the user selects on option in the filter it generates a dataset with a grouping on the filter selection -- i.e. when the user selects month the dataset is generated based on a grouping of the assicuated date field on a month basis. 

I know that grouping is used a lot with aggregation. I don't need to focus on that as I know what I want with the dataset after grouping is applied. My main question is how do you do GYNAMIC grouping. I.E. a dataset that is not generated with a fixed grouping but one that is generated with a selected grouping.

[petersi · Accepted solution]

I have determined how to do what I was looking for. The paramter configuration provides the ability to provide dynamic paramters that can be used in the query operation. In the Advanced Editor for a query you can integrate dynamic properties.