Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How Many Drivers Available

Hi, 

 

I have jobs table listed like below:

 
 

 

I have another table called driver log that looks like the below:

 

So in pivot above, how do I put a new measure called "Available Drivers".

So pivot should look like this:

 

Appreciate the help.

 

Regards,

Bryan

  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous Use this measure to include zone as well in available driver calculation. If it does not work please share sample data to reproduce the scenario

    Measure = 
VAR _jobDate = MAX(Job[Job date time])
VAR _zone = SELECTEDVALUE(Job[zone])
RETURN CALCULATE(COUNT(DriverLog[Driver id]),FILTER(DriverLog,DriverLog[Login]<=_jobDate && DriverLog[Logout]>=_jobDate && DriverLog[zone]=_zone))

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

    please create a measure as per below

    Measure = 
VAR _jobDate = MAX(Job[Job date time])
RETURN CALCULATE(COUNT(DriverLog[Driver id]),FILTER(DriverLog,DriverLog[Login]<=_jobDate && DriverLog[Logout]>=_jobDate))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous thanks so much, it seems to be working. Just 1 little issue I have.

       

      Currently, in jobs table, there is a field called 'zone'. This field is used as a slicer in the report.

       

      In driver log table, there is also a field called 'zone'

       

      How to make the measure to also consider selected slicer in the report?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous Use this measure to include zone as well in available driver calculation. If it does not work please share sample data to reproduce the scenario

        Measure = 
VAR _jobDate = MAX(Job[Job date time])
VAR _zone = SELECTEDVALUE(Job[zone])
RETURN CALCULATE(COUNT(DriverLog[Driver id]),FILTER(DriverLog,DriverLog[Login]<=_jobDate && DriverLog[Logout]>=_jobDate && DriverLog[zone]=_zone))