cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Have 1 filter for both column

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
1 ACCEPTED SOLUTION
Resident Rockstar

Hi @HKPBI , 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.

2 REPLIES 2
Resident Rockstar

Hi @HKPBI , 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.

Frequent Visitor

Thanks, it worked.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.