Filtering two unrelated tables

[CloudMonkey]

Hi All,

I need to calculate the total number of business days that company cars were in the garage being serviced (for a given month). To do this I need to:

1. filter the cars by the relevant vehicle type, then

2. for each car, count the number of days in the calendar that were not weekends/public holidays

Please can you tell me how to do part 1? In the below code in the sumx I've used the raw table 'Vehicles - Off-road' but this should have been filtered by the relevant vehicle type first - please can you tell me how to do that?

Thanks

CM

[v-stephen-msft]

Hi @CloudMonkey ,

The referenced column after the Filter function must be in the previous table. As I circled as shown in the figure below, what you filter is the calendar table, and then your filter criteria should be the columns in the calendar table, not the columns in the 'Vehicles-Off-road' table.

Since there is no data model, I can only suggest that you try to filter the 'Vehicles-Off-road’ table in SUMX.

Like below

calculate(sumx(filter('Vehicles-Off-road’, 'Vehicles-Off-road’[Vechicle Type]=" " && 'Vehicles-Off-road’[VORDate]....

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@CloudMonkey Can you provide a screenshot of your relationships data model view and a few rows of sample data pasted directly into the post as well as mock up of final result? 

Also, please paste your DAX directly into post rather than, or in addition to, screenshot so we can copy and modify it more easily. 

You are doing

SUMX('Vehicles - Off-road', ...

but if I understand your question correctly I think you want to do

SUMX('Calendar (Dates)', ...

Filtering by the relevant vehicle type should happen automatically with the relationships, so if the tables are unrelated as you stated in the subject of this post, please explain why and how we find the 'relevant vehicle type' from the desired end result you want. Hope that makes sense?

Cheers!