Column headers as filters

[samoberoi]

Hi,

For example i have total amount column and i have two other columns as start date and end date. How can i use there start and end date columns as filters so that when i choose any of those the total amount column dynamically changes with choosing either start date or end date column. Is that possible in any way.

Thanks

[parry2k]

@samoberoi ofcourse that will happen, I provided based on the question you asked, then easier is to use field parameters:

create two measures as shown below and then create a field parameter and include these two measures in the field parameters then you can use the field parameter in the visual and also in the slicer:

Start Date Amount = SUM ( Table[Start Date Total Amount] )

End Date Amount = SUM ( Table[End Date Contract Amount] )

[parry2k]

@samoberoi this makes more sense. You need to unpivot your data which you can easily do in PQ. If you have other columns in your table, select all those columns, right-click, and unpivot other columns. If you have only these four columns in your table, add an index column in PQ select this index column, right click and unpivot other columns.

Once unpivoting is done, you will get two columns, attribute and value, rename these as you see fit. Close and apply.

You can use attribute columns in the slicer and value column to show the total and easily work with this.

[samoberoi]

Hi, 

It doesn't work, i am afraid. When i try to unpivot the columns most of the columns from the table just disappear on PBI desktop and all the measures previously created come up showing error as well.

Thanks

[parry2k]

@samoberoi I think I know what you are looking for but before I provide any solution, can you paste sample data with the expected data based on the selection?

[samoberoi]

Hi Parr2k,

start Date           End Date            Start date Total amount                 End date contract amount

01/01/2008        31/07/2010        £40,000                                          £26,530

28/02/2008        06/09/2011        £50,000                                           £9230

30/04/2009        09/09/2013        £10,000                                          £5028

31/07/2009        05/04/2020        £1000000                                       £10000

09/08/2010        09/11/2016         £980000                                         £250

                                                     ----------------------------------------------

                                                     £2080000                                      £51038

• Start Date       
•  End Date

Here is an example of using Start Date and End Date column names as filters, so if i would click on Start Date highlighted in red, assuming it would be a filter, it should give me the total of Start Date column only and similarly if i click on End Date, assuming as a filter here, it should give me total for End date column only.

Thanks

Sam

[Anonymous]

Hi @samoberoi ,

As far as I know, if you want to filter the data same as selected Start Date and End Date. You can use [Start Date] and [End Date] to create two slicers directly.

Do you want to filter the data in the range of Start Date and End Date? Then you will meet a issue that there could only be an active relationship between two tables.

Measure:

Measure = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Start Date]<=MAX(DimDate[Date]) && 'Table'[End Date]>=MAX(DimDate[Date])))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samoberoi]

Hi

Not really. I don't want to filter the data between the chosen date from the start date column and chosen date from the End date column. However, i want to know if i can create the Start Date (Name of the column) as a filter, so that on clicking this name (Start Date) it will dynamically give me the results for Total amount column specific to the Start Date column and similarly for the End Date. 

Hope i could explain it a bit better this time and sorry for confusing everyone before.

Thanks

[Ashish_Mathur]

Hi,

In the Query Editor, you should create one row for each date which falls between the start and end.

[samoberoi]

Hi Ashish, 

Sorry, i couldn't understand what you mean by creating one row for each date and how will that work out. Apologies, i couldn't explain it before, i want the column names of Start date and End date to be used as filters. So, if i could make Start date name of the column as a filter and click on it, it should only give the results of Start Date in the Total amount and same for End date column name. 

Thanks

[Ashish_Mathur]

Hi,

With there be 2 dates columns, you will not be able to create ne slicer from dates in both those columns.  My suggestion is that we explode the table into a numebr of rows where each row will containe each date which falls between the start date and end date.