Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

No results returned - USERELATIONSHIP

Hi,

 

Trying to get the total number of people (carers) starting & leaving the company.

 

I have 2 tables - A Calendar table & a Carers table.

 

I have a Many to one inactive rel-ship between the startdate & date columns in the Carers table & Calendar table.

I have a Many to one inactive rel-ship between the deleteddate & date columns in the Carers table & Calendar table.

 

Carers Started = CALCULATE(DISTINCTCOUNT('powerbi114 carer'[id]),
USERELATIONSHIP('powerbi114 carer'[startDate],'Calendar'[Date]))
 
Terminated Carers = CALCULATE(DISTINCTCOUNT('powerbi114 carer'[id]),
USERELATIONSHIP('powerbi114 carer'[deleted],'Calendar'[Date]))
 
I get the correct results for Carers Started measure but get no results for Terminated Carers measure and have no idea why.
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , check the date 'powerbi114 carer'[deleted]  do not have timestamp. Change datatype to DateTime  and the format to include time

     

    If there is then create a date

    deleted date = 'powerbi114 carer'[deleted].date

    or

    deleted date = date(year( 'powerbi114 carer'[deleted]),month( 'powerbi114 carer'[deleted]),day( 'powerbi114 carer'[deleted]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I had thought of that and had no timestamp.

       

      But I did needed to remove the timestamp in the query editor.

       

      Now it works.

       

      Thanks a milllion!