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 All,
I've read all of the previous threads in regards to this issue however I did not find any good solution that works for my setup.
I have two tables:
Sheet 1:
Change Date ID
05 May 2019 | 123 |
05 May 2018 | 123 |
03 February 2019 | 567 |
Sheet 2:
ID Start Date End Date Name
123 | 01 January 2019 | 31 December 9999 | Red |
123 | 01 January 2018 | 31 December 2018 | Blue |
567 | 01 January 2018 | 31 December 9999 | Orange |
I want to create a calculated column that returns the Name in Table 1 for each ID that falls into the date range.
Sheet 1 including calculated column should look like that:
Change Date ID Name
05 May 2019 | 123 | Red |
05 May 2018 | 123 | Blue |
03 February 2019 | 567 | Orange |
I have tried the following two approaches:
1:
Calc Name 1 works if I put a filter on Query for Sheet 2 to display only one of the IDs e.g. 123.
The moment I take off the filter in the query I get multiple values returned instead of the one that was expected.
What step am I missing here?
Thanks!
Solved! Go to Solution.
@Anonymous
Try this revision
Calc Name 1 = CONCATENATEX ( CALCULATETABLE ( VALUES ( Sheet2[Name] ), FILTER ( ALL ( Sheet2 ), Sheet1[Change Date] >= Sheet2[Start Date] && Sheet1[Change Date] <= Sheet2[End Date] && Sheet1[id] = Sheet2[id] ) ), [Name], "," )
That worked for me too! I had a very similar issue, and I alse was trying to use a DAX very similar to what @Anonymous was trying. It is funny how we cannot use something like LOOKUPVALUE to achieve those needs of retrieving certain values... In excel, a PROVC would do the work
@Anonymous
Try this revision
Calc Name 1 = CONCATENATEX ( CALCULATETABLE ( VALUES ( Sheet2[Name] ), FILTER ( ALL ( Sheet2 ), Sheet1[Change Date] >= Sheet2[Start Date] && Sheet1[Change Date] <= Sheet2[End Date] && Sheet1[id] = Sheet2[id] ) ), [Name], "," )
Terrific it works!
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |