Getting data from different tables using measure

 To create a measure in Power BI that retrieves the confirmed_date from table2 based on the ticket_num in table1, you can use DAX (Data Analysis Expressions). Here is a step-by-step guide:

1. Open Power BI Desktop and load your data tables (table1 and table2).

2. Create Relationships:

   • Go to the "Model" view.
   • Create a relationship between table1 and table2 using the ticket_num column.

3. Create a New Measure:

   • Go to the "Report" view.
   • Click on the table1 in the Fields pane.
   • Click on "New Measure" in the ribbon.

4. Write the DAX Formula:

   • In the formula bar, enter the following DAX formula to create the measure:

Confirmed Date Measure = 
CALCULATE(
    MAX(table2[confirmed_date]),
    FILTER(
        table2,
        table2[ticket_num] = SELECTEDVALUE(table1[ticket_num])
    )
)

This measure does the following:

• CALCULATE is used to change the context in which the data is evaluated.
• MAX(table2[confirmed_date]) retrieves the maximum confirmed date from table2. Since table2 has a one-to-many relationship with table1, this will effectively retrieve the confirmed date for the corresponding ticket_num.
• FILTER(table2, table2[ticket_num] = SELECTEDVALUE(table1[ticket_num])) filters table2 to match the ticket_num from the currently selected row in table1.

5. Use the Measure:
   • You can now use the Confirmed Date Measure in your reports, tables, or visualizations to display the confirmed date from table2 for each ticket_num in table1.

This approach ensures that the confirmed date from table2 is correctly associated with the corresponding records in table1 based on the ticket_num column.