Forum Discussion
Lookup lowest date
I have two tables, 1 where the user submits their ISP information and there can be multiple entries if they change the speed, provider, sometimes they upload a new record monthly, and another table where they submit any downtime they have during their working hours.
Now I want to know the ISP associated with the downtime, the two tables are something like:
TABLE 1
| AGENT ID | ISP | Date |
| 1 | Provider 1 | 9/1/21 |
| 1 | Provider 2 | 10/17/21 |
TABLE 2
| AGENT ID | DOWNTIME DATE | REASON | ISP Lookup |
| 1 | 9/5/21 | Internet Issues | |
| 1 | 9/20/21 | Internet Issues | |
| 1 | 10/1/21 | Internet Issues | |
| 1 | 10/31/21 | Internet Issues | |
| 1 | 11/5/21 | Internet Issues | |
| 1 | 12/12/21 | Internet Issues | |
| 1 | 12/13/21 | Internet Issues |
I want to make a Lookup that finds the ISP based on the lowest date, In this example, everything before 10/17/21 should go under provider 1 and everything after 10/17/21 should be under provider 2
I'm having issues getting the date match, can you help me with this?
Hi Anonymous
Try this code to add a new column with DAX =
ISP Lookup = CALCULATE ( MAX ( 'Table 1'[ISP] ), FILTER ( 'Table 1', 'Table 1'[Date] <= EARLIER ( 'Table 2'[DOWNTIME DATE] ) ) )output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
2 Replies
Hi Anonymous
Try this code to add a new column with DAX =
ISP Lookup = CALCULATE ( MAX ( 'Table 1'[ISP] ), FILTER ( 'Table 1', 'Table 1'[Date] <= EARLIER ( 'Table 2'[DOWNTIME DATE] ) ) )output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/