Forum Discussion

Bobstar86's avatar
Bobstar86
New Member
4 years ago

Selecting dynamic colums based on slicer selection

Hi

I’m looking for helping to write a DAX measure that selects dynamic columns based on slicer selection.

I have a large dataset on power query that has hundreds of columns. I’m creating a dashboard where one of the slicers will be ‘dates’ allowing user to enter a ‘from’ (start date) and ‘to’ (end date).

Once user enters the dates, the DAX measure should do the following:

  1. Filter the main data set and produce a subset data table based on the user’s selection.  
  2. Add a new column that calculates percentage return between these two dates. The percentage return will be ‘Return = Divide ((end date, start date – 1*100)’.  

For visualisation, this is how the main data looks like:

Portfolio31/07/202001/08/202002/08/202003/08/202004/08/202005/08/2020
P1100.0100.5101.0101.5102.0102.5

P2

100.0101.0102.0103.0104.0105.0
P3100.0105.0110.0115.0120.0125.0

If a user selects start date as 02/08/2020 and end date as 04/08/2020, the expected results is as tabled below. The first column (‘Portfolio’) will be static whilst the columns with header dates will be dynamic based on user selections.

Portfolio02/08/202004/08/2020Return
P1101.0102.01.0
P2102.0104.02.0
P3110.0120.09.0

I would really appreciate your help in writing a measure that can do the above.

 

Thanks

 

B

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Bobstar86 ,

     

    I suggest you unpivot your table by select all other columns except [Portfolio] column.

    New table should look like as below.

    Measure:

    Measure = 
VAR _StartDate = SELECTEDVALUE('Start Date'[Start Date])
VAR _EndDate = SELECTEDVALUE('End Date'[End Date])
VAR _Value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date] = _StartDate ||'Table'[Date] = _EndDate))
VAR _StartValue = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date] = _StartDate))
VAR _EndValue = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date] = _EndDate))
RETURN
IF(ISFILTERED('Start Date'[Start Date])||ISFILTERED('End Date'[End Date]),IF(HASONEVALUE('Table'[Date]),_Value,_EndValue - _StartValue),IF(HASONEVALUE('Table'[Date]),SUM('Table'[Value])))

    Create a matrix visual and rename column subtotal as Return. Create two unrelated date tables to create start date slicer and end date slicer.

    Result is as below.

    By Default:

    Startdate =2020/08/02, Enddate =2020/08/04

     

    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.

     

     

    • Bobstar86's avatar
      Bobstar86
      New Member
      4 years ago

      Hi Anonymous 

      This is very helpful and is exactly what I was after. Thanks very much.

      If you don't mind, I have a few follow up questions that I would appreciate your help with:

      1. Do I therefore need to set the data source as an unpivoted table?
      2. Is there a way of preventing user from selecting multiple dates?
      3. Is there a way to set the date slicers such that a user cannot input a ‘To’ date earlier than 'From' date?
      4. My dataset has hundrends of date columns (in pivoted format). Setting the date slicer as drop down means there will be alot of scrolling up and down to select a date. Setting the slicer as between dates (similar to below) with a calendar is much more efficient. Is there a way of doing this instead of the drop down.

      Once again thank you so much

       

      Bobstar86