Forum Discussion

RobJ1187's avatar
RobJ1187
New Member
5 years ago
Solved

Training tracker (Covid responders please help!)

First time posting so bear with me. We're currently pulling together completion dates for all the steps needed to deploy vaccinators to support the health service and our admin team is completely overwhelmed. They're using an excel sheet in a team to track dates and spending a lot of time answering update queries from the volunteer unit managers around the country as they can't access the master data.

 

We want to provide a PowerBI dashboard managers can search by person to get an update so they can focus on deploying the vaccinators. Ideally we want fully deployable column to be rule based and go true when all dates are entered but i'll be honest i'm struggling to identify a data validation rule that will work as i'm not an excel ninja. 

 

Any advice would be really welcome as we've got about 30000 people to come through and admin is killer!

 

Person NoFirst NameSurnameRoleEmailRegionDistrictAreaUnitFully DeployableSJA E-learningLegal AspectsVacc StorageVacc AdminCore KnowledgemRNAAstra Zeneca
12345JoeBloggsVolunteer Vaccinator[email protected]NorthGreater ManchesterNorth VirtualGM-10TRUE12/01/2115/01/21    13/01/21
Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi RobJ1187 ,

    You could create a calculated column in PBI using the following formula

    Deployable= if([SJA E-learning]<> BLANK() && [Legal Aspects]<>BLANK()&&[Vacc Storage]<>BLANK()&&[Vacc Admin]<>BLANK()&&[Core Knowledge]<>BLANK()&&[mRNA]<>BLANK()&&[Astra Zeneca]<>BLANK(), TRUE(), FALSE())

     or a measure like

    Deployable Measure = 
if(HASONEVALUE('Table'[Person No]), 
    if(Values('Table'[SJA E-learning])<> BLANK() 
    && Values('Table'[Legal Aspects])<>BLANK()
    && Values('Table'[Vacc Storage])<>BLANK()
    && Values('Table'[Vacc Admin])<>BLANK()
    && Values('Table'[Core Knowledge])<>BLANK()
    && Values('Table'[mRNA])<>BLANK()
    && Values('Table'[Astra Zeneca])<>BLANK(), TRUE(), FALSE())  )

    Hope this helps,

     

3 Replies

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi RobJ1187 ,

    You could create a calculated column in PBI using the following formula

    Deployable= if([SJA E-learning]<> BLANK() && [Legal Aspects]<>BLANK()&&[Vacc Storage]<>BLANK()&&[Vacc Admin]<>BLANK()&&[Core Knowledge]<>BLANK()&&[mRNA]<>BLANK()&&[Astra Zeneca]<>BLANK(), TRUE(), FALSE())

     or a measure like

    Deployable Measure = 
if(HASONEVALUE('Table'[Person No]), 
    if(Values('Table'[SJA E-learning])<> BLANK() 
    && Values('Table'[Legal Aspects])<>BLANK()
    && Values('Table'[Vacc Storage])<>BLANK()
    && Values('Table'[Vacc Admin])<>BLANK()
    && Values('Table'[Core Knowledge])<>BLANK()
    && Values('Table'[mRNA])<>BLANK()
    && Values('Table'[Astra Zeneca])<>BLANK(), TRUE(), FALSE())  )

    Hope this helps,

     

    • RobJ1187's avatar
      RobJ1187
      New Member
      5 years ago

      That's fantastic thank you! Any tips on summary count dates vs blanks for an overview? I.e. for all the vaccinators how many have a date against it and how many are blank. As it's dates and not a numerical 1/0 it's causing me some trouble when i'm trying to use the donut. As an example i've currently got it in true/false which i can now flip to dates (thanks!)

       

      • richbenmintz's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        5 years ago

        Hi RobJ1187,

         

        Not sure I understand the requirement, but you could use the countrows() function in conjunction with filtering to create measures that count rows where dates exist