cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mlrossi Regular Visitor
Regular Visitor

Custom slicer that toggles between columns to generate a chart

 

 

Hello,

 

I am trying to generate a customer slicer that can be used to toggle between date columns to generate a time sensitive chart of counts per day based on the selected date field from the slicer.

 

I created the customer slicer using a measure table with the fully qualified date field (i.e., table_name[date_selection]), but am having problems tying the slicer selection to the date column in the chart using the measure date column.  I would love help on the path forward using this approach or any suggestions for an alternative approach that might be better.  Any help you can provide would be greatly appreciated.  Thank you!

 

 

first datelast daterecent dateproduct code
6/20/201811/9/201810/10/20181111
6/20/201811/9/201811/9/20181111
4/1/201810/30/201811/2/20181121
6/20/20186/30/201811/10/20181121
10/30/201811/2/201811/7/20181141
6/20/201811/9/201811/9/20181141
4/1/201811/9/201811/9/20181141
10/30/201811/3/201811/9/20181141
5/20/20186/20/20188/1/20181151

 

Slicer

First Date  Last Date  Recent Date

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Custom slicer that toggles between columns to generate a chart

Hi @mlrossi,

 

Add a calendar table and a table for slicer selection with the following data:

 

Slicer Selection

first date
last date

recent date

 

Then add the following measure:

Date selection = 
SWITCH (
    MAX ( slicers[Slicer Selection] );
    "first date"; CALCULATE (
        COUNT(Table1[product code]);
        FILTER (
            ALL ( Table1[first date] );
            Table1[first date] = MAX ( 'Calendar'[Date] )
        )
    );
    "last date"; CALCULATE (
        COUNT(Table1[product code]);
        FILTER (

 ALL ( Table1[last date] ); 
            Table1[last date] = MAX ( 'Calendar'[Date] )
        )
    );
    "recent date"; CALCULATE (
        COUNT(Table1[product code]);
        FILTER (
            ALL ( Table1[recent date] );
            Table1[recent date] = MAX ( 'Calendar'[Date] )
        )
    )
)

Replace the 

Count( Table1[product code] )

 By the other measure you want to calculate sum, max, min, whatever then just add the slicer of the calendar table and another one for the slicer table and should work.

 

see attach PBIX file

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Custom slicer that toggles between columns to generate a chart

Hi @mlrossi,

 

Add a calendar table and a table for slicer selection with the following data:

 

Slicer Selection

first date
last date

recent date

 

Then add the following measure:

Date selection = 
SWITCH (
    MAX ( slicers[Slicer Selection] );
    "first date"; CALCULATE (
        COUNT(Table1[product code]);
        FILTER (
            ALL ( Table1[first date] );
            Table1[first date] = MAX ( 'Calendar'[Date] )
        )
    );
    "last date"; CALCULATE (
        COUNT(Table1[product code]);
        FILTER (

 ALL ( Table1[last date] ); 
            Table1[last date] = MAX ( 'Calendar'[Date] )
        )
    );
    "recent date"; CALCULATE (
        COUNT(Table1[product code]);
        FILTER (
            ALL ( Table1[recent date] );
            Table1[recent date] = MAX ( 'Calendar'[Date] )
        )
    )
)

Replace the 

Count( Table1[product code] )

 By the other measure you want to calculate sum, max, min, whatever then just add the slicer of the calendar table and another one for the slicer table and should work.

 

see attach PBIX file

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mlrossi Regular Visitor
Regular Visitor

Re: Custom slicer that toggles between columns to generate a chart

MFelix,

 

It took me a little time to get it implemented and fixed.  Your solution is golden.  Thank you Soooo much for your assistance.  I was close, but you got me straight and your solution worked like a dream.  I truely appreciate it!!

 

ML