cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
salimgs Frequent Visitor
Frequent Visitor

Accessing Slicer filtered values through columns

I have a Dates table that lists daily dates in a range. I put a slicer on it.

I have a second table (lets call it Items) that is not related to Dates table but I need to have access to minimum and maximum filtered range of Dates selected by slicer.

If I define a measure =MAX(Dates[Date]) the value changes as the slicer changes.

But if I define a column in Items table, MAX(Dates[Date]) expression ignores slicer's range.

 

How can I access Slicer filtered values through columns?

 

PS: In my scenario I cannot set up a relationship between Dates and Items tables because I have multiple date columns in Items that cannot have a one-to-many relationship with Dates.

5 REPLIES 5
Microsoft Phil_Seamark
Microsoft

Re: Accessing Slicer filtered values through columns

HI @salimgs

 

Why does it have to be a column?  I wonder if you can achieve the effect you are after with a measure after all?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

salimgs Frequent Visitor
Frequent Visitor

Re: Accessing Slicer filtered values through columns

Thanks @Phil_Seamark for your response.


I can achive the effect with measure but I need it in column because I want to create a "flag" column to put it in visual filter pane.

 

Here is the scenario:

Date table has just one [date] field holding all posible dates. I have a slicer on this.

Items table has [Title], [forecast start date] [forecast finish date] [actual start date] [forecast start date]

Now I want to show a table with just the rows that at least one of its four date columns are in the sliced date range.

 

I can make a measure like this and it works:

Flag = CALCULATE(HASONEVALUE(Items),FILTER(Items, Items[forecast start date]>=(MIN(Date[date] && Items[forecast start date]<=MAX(Date[date]) || (Items, Items[forecast finish date]>=MIN(Date[date] && Items[forecast finish date]<=MAX(Date[date]))

But I cannot use this measure to visually filter my rows in Items (or at least don't know how to).

 

Any idea?

Moderator v-caliao-msft
Moderator

Re: Accessing Slicer filtered values through columns

@salimgs,

 

You could create a measure like:

Measure = IF(MAX('Item'[forecast start date])>='Table'[MinDate]&&MAX('Item'[forecast start date])<='Table'[MaxDate],1,0)+IF(MAX('Item'[forecast finish date])>='Table'[MinDate]&&MAX('Item'[forecast finish date])<='Table'[MaxDate],1,0)+IF(MAX('Item'[actual start date])>='Table'[MinDate]&&MAX('Item'[actual start date])<='Table'[MaxDate],1,0)+IF(MAX('Item'[autual finish date])>='Table'[MinDate]&&MAX('Item'[autual finish date])<='Table'[MaxDate],1,0)

Then add this measure to your visual filter
Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

salimgs Frequent Visitor
Frequent Visitor

Re: Accessing Slicer filtered values through columns

Hi Charlie,

 

Thanks a lot for your detailed answer. Your solution works but I noticed my solution also works if I use IF(A,1,0)+IF(B,1,0) instead of A || B. Please see attached. If we define a Boolean measure, the visual level filter does not comprehend that. Get the file from here: https://ufile.io/dl5bz

 

Untitled.png

salimgs Frequent Visitor
Frequent Visitor

Re: Accessing Slicer filtered values through columns

Is this a bug? How we should report it?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors