Forum Discussion

kb19270's avatar
kb19270
Frequent Visitor
4 years ago
Solved

Creating a table using a measure

I want to create a table using a measure i created. The measure gives the number of employees per month and now I want to get it in a table. 

This is how i calculated the measure: 

 

TM_Count = VAR selectedDate = MAX('Dates'[Date])

RETURN

SUMX('info TeamMembers',
VAR employeeStartDate = 'info TeamMembers'[HireDate]
VAR employeeEndDate = 'info TeamMembers'[TermDate]
RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0))
 
Is it possible to do this?
Any help will be appreciated. 
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, kb19270 

    You can't filter the data value in a calculated table but you can apply a filter to a table visual filter pane.

    Please add another measure:

    visaul filter = 
VAR selectedDate = MAX('Dates'[Date])
VAR employeeStartDate = MAX('info TeamMembers'[HireDate])
VAR employeeEndDate = MAX('info TeamMembers'[TermDate])
RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)

    Then apply it to table visual filter pane.

     

    Best Regards,
    Community Support Team _ Eason

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, kb19270 

    You can't filter the data value in a calculated table but you can apply a filter to a table visual filter pane.

    Please add another measure:

    visaul filter = 
VAR selectedDate = MAX('Dates'[Date])
VAR employeeStartDate = MAX('info TeamMembers'[HireDate])
VAR employeeEndDate = MAX('info TeamMembers'[TermDate])
RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)

    Then apply it to table visual filter pane.

     

    Best Regards,
    Community Support Team _ Eason