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.
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.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |