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.
Hello,
can anyone please help out in the following problem?
Got 2 tables related to this task:
1) Date
2) unique contracts with valid from date(start of month) and valid to (end of month).. The range varies from several years to months.
Basically need to filter out those contracts that are valid in the selected period (Year-Month)- selection from slicer?
My approach was getting the value from the slicer and sending it into the query defining the new table. The problem however is that my Selected Period is a measure and the table does not get affected by the changes on visual. My dax formula is below.
Lookng into an alternative working solution. Is there any chance I can loop every individual rows from date table and comparing it against indvidual rows (falls between the valid from & to dates or not) in Contracts - ie: nesting 2 row contexts between 2 different tables? The result would be a new table with combination of Contracts and Year-Months. Then I can join the new table with my contract table and can do the filtering based on relationship? Or any other solution to this problem you can think of?
Thanks in advance!
Tomas
Solved! Go to Solution.
Hi @TomasSamu ,
If you want to filter the contract table based on date slicer, you can create this control measure and set its value as 1 in the visual filter:
Control =
VAR _Date =
SELECTEDVALUE ( 'Date'[Date] )
VAR startdate =
SELECTEDVALUE ( ContractConfiguration[Start] )
VAR enddate =
SELECTEDVALUE ( ContractConfiguration[End] )
RETURN
IF ( _Date = BLANK (), 1, IF ( _Date > startdate && _Date < enddate, 1, 0 ) )
Sample file is attached that hopes to help you: Nest filtering.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @TomasSamu ,
If you want to filter the contract table based on date slicer, you can create this control measure and set its value as 1 in the visual filter:
Control =
VAR _Date =
SELECTEDVALUE ( 'Date'[Date] )
VAR startdate =
SELECTEDVALUE ( ContractConfiguration[Start] )
VAR enddate =
SELECTEDVALUE ( ContractConfiguration[End] )
RETURN
IF ( _Date = BLANK (), 1, IF ( _Date > startdate && _Date < enddate, 1, 0 ) )
Sample file is attached that hopes to help you: Nest filtering.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
107 | |
98 | |
78 | |
64 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |