Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
MFelix
Super User
Super User

Hi @Anonymous,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @Anonymous,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.