Level measure at a specific level

[Anonymous]

Hi,

I have a employee table which is associated with Program's table. These together will provide the information about the employees who participated or not participated on program sessions. I have built a measure for number of employees who participated on sessions which gets changed according to program name selection and I would like to build a measure to count the total number of employees on employee table which never changes according to program name. 

To be precise I have a slicer on top of program name and I have a card on top of number of total employees. When the user choses a specific program on the slicer, the count on card should not change whereas it will be static always. How can I achieve this ? 

In microstrategy I can create a level metric at employeeid level, so the metric will always produce the same count regardless of program names. 

[v-frfei-msft]

Hi @Anonymous ,

What is the relationship between employee table and Program's table. How about setting the relationship to both to check again?

[Anonymous · Accepted solution]

The following CROSS FILTER function worked which isolates employee from program_participation.

Number of Employees - Demographics Level = CALCULATE(DISTINCTCOUNT(Employee[Employee_ID]), CROSSFILTER(Employee[Employee_ID], Program_Participation[Employee_ID], none))

[Anonymous]

@Anonymous - You can create a new measure, that will be used in the card. It will look something like the following. Note: This will always show the number of rows in the employee table - never affected by any slicers.

Number of Employees = COUNTROWS(ALL('Employee'))

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.