Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
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?
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
Regards,
Charlie Liao
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
Is this a bug? How we should report it?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |