How do I use a slicer to filter more than one column?

[Anonymous]

I have data similar to the following:

Record--------Date 1----------Date 2----------Date 3

A--------------1/1/2020-------2/5/2020-------1/20/2020

B--------------1/10/2020------2/7/2020-------1/29/2020

C--------------2/3/2020-------2/7/2020-------2/6/2020

D--------------1/2/2020-------1/5/2020-------3/8/2020

E--------------3/1/2020-------1/10/2020------1/5/2020

I want to have one slicer that allows me to pick a date range.

I have three cards, each on displaying a differnt count of the records based on the date range picked on the slicer.

• The first card will count the number of records based on the number of times a date in the column Date 1 falls within the date range selected with the slicer.
• The second card will count the number of records based on the number of times a date in the column Date 2 falls within the date range with the slicer.
• The third card will count the number of records based on the number of times a date in the column Date 3 falls within the date range with the slicer.

I know how to do this with three different slicers. I want to be able to use only one slicer.

Can anyone help?

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description,I think you can create three measures,then put them in card.You don't need to create relationships.

Like this:

card1results =
VAR a =
    DISTINCT ( 'Table 2'[Date] )
RETURN
    COUNTX ( FILTER ( 'Table', [Date1] IN a ), [Date1] )

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@manikumar34  and @amitchandak ,

I don't think I did a good job at describing the results I wanted. Here is a clearer example. In the data below, the dates that are bolded are the ones that fall within the date range on the slicer. They are ones that should be counted on the cards.

DATA:

Record--------Date_1----------Date_2----------Date_3

A--------------1/1/2020-------2/5/2020-------1/20/2020

B--------------1/10/2020------2/7/2020-------1/29/2020

C--------------2/3/2020-------2/7/2020-------2/6/2020

D--------------1/2/2020-------2/25/2020-------3/8/2020

E--------------3/1/2020-------1/10/2020------1/5/2020

DATE RANGE SELECTED ON SLICER:

2/1/2020 - 3/1/2020

CARD 1 RESULTS

Record Count: 2

CARD 2 RESULTS

Record Count: 4

CARD 3 RESULTS

Record Count: 1

Please Note: I have already created a date table that includes all the relevant dates. I did this by takin all the dates from Date_1, Date_2 and Date_3 and appended them into one table. Then I created a new table based on the minimum and maximum dates in the appended table.

[manikumar34]

@Anonymous , 

You have to use Relative date and use that relative date to create dynamic calculations for the cards. 

If you know the dates for all the 3 cards you can use this way. 

relative date = DATEDIFF(MAX(Tbale[Date]),MIN(Table[Date]),DAY)

Card1 = SUM(Table[Sales})

Card2 = Calculate(SUM(Tba;e[Sales]),DATESINPERIOD(Table,LASTDATE(Table[Date}),-7*[relative date],DAY))

Card3 = Calculate(SUM(Tba;e[Sales]),DATESINPERIOD(Table,LASTDATE(Table[Date}),-14*[relative date],DAY))

Here I have given an example for calculating 3 different times a week apart. 

Regards, 

Manikumar

[amitchandak]

@Anonymous , You need to create a date table and join all three dates with it. One date will active and others will be inactive, you can activate that using userelationship

Refer: https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970