Forum Discussion
Helper IIIDax Table Filterting
Hello all,
I have a table like so:
I have connected it to a date table all the start and thru columns on the right side are connecting to the Date column on the left:
And I created a dax table like so:
I was hoping to use a date slicer and filter the table down but nothing happens when I do that. Any ideas as to why the date table is not filtering the table properly?
7 Replies
- parry2k
Super User
jeronimo2334 you cannot use slicer value or dynamically create calculated tables based on the slicer selection.
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
jeronimo2334Is there any alternative ways to do something like this? I'm basically interested in finding out what row corresponds on the table whenever I use a date slicer.
- parry2k
jeronimo2334 it will be much easier put few columns with sample data and expected output.
v-kelly-msftCommunity Support
Hi jeronimo2334 ，
I made a simple sample as below:
Create a measure as below:(Be sure that if you wanna use a measure to filter data,you'd better remove the relationship between the key fields)
measure = var _maxdate=CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'[Date])) var _mindate=CALCULATE(MIN('Date'[Date]),ALLSELECTED('Date'[Date])) var _tab= CALCULATETABLE(VALUES('Table'[Crstart]),FILTER(ALL('Table'),'Table'[Crstart]<=_mindate&&'Table'[Crthur]>=_maxdate)) Return IF(MAX('Table'[Crstart]) in _tab,MAX('Table'[Crstart]),BLANK())And you will see:(put measure into filter pane and select measure is not blank)
For the related .pbxi file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- jeronimo2334
Attached some sample data:
I have Table 1 and Table 2. What I would like to do is in Table 1 attach a new column with the giveName column from Table 2. The tricky part is that the records need to match on these criteria:
PostedDate of Table 1 is less or equal from the date columns in Table 2 that have the "Start" prefix
and
PostedDate of table 1 is greater or equal from the "Thru" date columns or the "Thru" columns are null.
If there are more that 1 records that match, I'd like to concatane the names by a comma if possible.Table 1:
https://www.dropbox.com/s/qx8vvofcwqle5uq/table1.xlsx?dl=0
Table 2:
https://www.dropbox.com/s/pshwj3a3j4qdwst/table2.xlsx?dl=0- Ashish_Mathur
Hi,
Could you kindly show the expected result in a few rows of column H of the table1.xlsx file?
- jeronimo2334
Hi, I forgot to include something important in tables 1 & 2, the ID column. Table 1 and Table 2 should match on the ID column.
I added Table 3 which is what the expected result should be.
https://www.dropbox.com/sh/lgdxefabz139xsh/AADnr0RfSe-4YTWMs9Xjj34Ga?dl=0
Just for reference, if this was to be done via MySQL, the 'where' parameters would look like this
set @d = "2018-01-12";#example of posted
...WHERE cr.effectiveDateStart <= @d and (cr.effectiveDateThru is null or cr.effectiveDateThru >= @d) and c_r.effectiveDateStart <= @d and (c_r.effectiveDateThru is null or c_r.effectiveDateThru >= @d) and opi.effectiveDateStart <= @d and (opi.effectiveDateThru is null or opi.effectiveDateThru >= @d) and pi.effectiveDateStart <= @d and (pi.effectiveDateThru is null or pi.effectiveDateThru >= @d) and ui.effectiveDateStart <= @d and (ui.effectiveDateThru is null or ui.effectiveDateThru >= @d)
