PowerBi: Returning id in DAX formula

0
votes

I am a new PowerBi user and I would like to create a primary/foreign key relationship using a DAX formula which will return the id of the corresponding line to me.

I have two tables:

  1. CompanyXFlights
  2. GlobalFlights

The dax formula:

Exist =
COALESCE (
    CALCULATE (
        COUNTROWS ( CompanyXFlights );
        FILTER (
            CompanyXFlights;
            OR (
                AND (
                    GlobalFlights[ArrivalDateTime]
                        < CompanyXFlights[DateTime] + TIME ( 0; 10; 0 );
                    GlobalFlights[ArrivalDateTime]
                        > CompanyXFlights[DateTime] - TIME ( 0; 10; 0 )
                );
                AND (
                    GlobalFlights[DepratureDateTime]
                        < CompanyXFlights[DateTime] + TIME ( 0; 10; 0 );
                    GlobalFlights[DepratureDateTime]
                        > CompanyXFlights[DateTime] - TIME ( 0; 10; 0 )
                )
            )
                && GlobalFlights[AircraftRegistrationCode]
                       = CompanyXFlights[AircraftRegistrationCode]
        )
    );
    0
)

I'm using this formula to get the number of corresponding lines, but now I want it to return the index (id) of the line. What should I change? Should I put CompanyXFlights[Id] instead of COUNTROWS(CompanyXFlights) or should I use another formula?

1
powerbidaxpowerquery
A sample data or file would be help to perform further. – Rajat Jaiswal
Its simple if you take it this way: the calculate function count number of rows that respect the filter() and the filter is just a bunch of conditions. the thing is that i want, not to countrows, but to return 'Id' instead. So can i do that with CALCULATE or should i use another function ? – SELoug

1 Answers

0
votes

I found the solution, and it was quite simple, i just changed COUNTROWS(CompanyXFlights) by SELECTEDVALUE(CompanyXFlights[Id]);