Have 1 filter for both column

[Anonymous]

Hi team,

From the table 1, I would like to have result as table below. However, the issue is with the location. If my out location is AAA, the out location is not AAA. The aim is to see the difference between in & out forthe same location. Any suggesstion is much apprecaited. Thanks

Table 1:

Car Type	Out Location	In Location	Date
Sedan	AAA	BBB	Jan-23
Sedan	CCC	AAA	Jan-23
Sedan	AAA	BBB	Jan-23
Sedan	AAA	CCC	Feb-23

Result:

Month	Location	Car Type	Out	In	Var
Jan-23	AAA	Sedan	2	1	1

[Daryl-Lynch-Bzy · Accepted solution]

Hi @Anonymous , in your data model you should have a single Dimension table with all the Locations.  Create Two relationships on this Dimension table to the Table Fact (i.e. your first table above).  After this you can create the followig DAX measures:

Out = 
   CALCULATE ( 
      COUNTROWS ( Fact Table ) , 
      USERELATIONSHIP( dim_Location[LocationKey] , FACT Table[Location Out] ) 
   ) 

In = 
   CALCULATE ( 
      COUNTROWS ( Fact Table ) , 
      USERELATIONSHIP( dim_Location[LocationKey] , FACT Table[Location In] ) 
   ) 

Var = [Out] - [In]

Your table is created by using the Dimension Location field in the second matrix.

[Anonymous]

Thanks, it worked.