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 dashboar...
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