Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have one table with multiple fields which i want to display, two of the fields are from and to dates.
I wish to filter the data displayed using a week commencing date.
I created a separate table with two fields, date and week commencing date but can't figure out how to filter the other table using it.
E.g User selects wc 21/05/2017
I want to display all rows which have a start date < (21/05/2017 + 6) and a finish date > 21/05/2017
Hope that makes sense
Solved! Go to Solution.
Hi @JordB,
If I understand you correctly, you should be able to follow steps below to get your expected result.
I assume your first table with start/finish date called "Table1", and the second table with week commencing date called "Table2".
1. Remove the relationship between "Table1" and "Table2" if there is.
2. Use the formula below to create a measure to indicate if the rows have a start date < ( the selected wc + 6) and a finish date > selected sc.
IsSelected = VAR currentSelectWC = MAX ( 'Table2'[wc] ) RETURN CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( 'Table1', 'Table1'[start date] < currentSelectWC + 6 && 'Table1'[finish date] > currentSelectWC ) )
3. Then you should be able to use the measure [IsSelect] as a visual level filter on the visualization you're showing your fields, and apply "is greater than 0" to get only selected result.
Regards
Hi @JordB,
If I understand you correctly, you should be able to follow steps below to get your expected result.
I assume your first table with start/finish date called "Table1", and the second table with week commencing date called "Table2".
1. Remove the relationship between "Table1" and "Table2" if there is.
2. Use the formula below to create a measure to indicate if the rows have a start date < ( the selected wc + 6) and a finish date > selected sc.
IsSelected = VAR currentSelectWC = MAX ( 'Table2'[wc] ) RETURN CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( 'Table1', 'Table1'[start date] < currentSelectWC + 6 && 'Table1'[finish date] > currentSelectWC ) )
3. Then you should be able to use the measure [IsSelect] as a visual level filter on the visualization you're showing your fields, and apply "is greater than 0" to get only selected result.
Regards
Worked a treat, thanks.
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |