Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filtering two unrelated tables

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

 

 

Need Help

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    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.