Forum Discussion
Column headers as filters
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
10 Replies
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] )Hi,
In the Query Editor, you should create one row for each date which falls between the start and end.
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
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
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
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.
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
