CALCULATETABLE slicing alternative

[SteveBrou]

I currently have a table that contains information regarding job positions that are requisitioned. These tables lists are housed in SP as custom lists and follow a flow through recruiting to HR. I have created a dashboard that shows the records in various states, i.e. Open Positions, Positions with hires in progress, and filled positions. I have done this by creating tables using CALCULATETABLE. Ultimately I want to be able to filter these tables created using CALCULATETABLE by project using a slicer. I discovered that my slicer will not work with these tables. How can I best achieve my objective?

Example:

Open Positions
Date Initiated

Project Name

Job ID

Job TItle
Target Start Date

I create the Open Positions table by using CALCULATETABLE on another table called "Recruiting". The only filter that I am using with the CALCULATETABLE function on this table is "Status = 2". The slicer contains a table of Distinct project names. Any help would greatly be appreciated!

[v-zhenbw-msft]

Hi @SteveBrou ,

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@SteveBrou , You can not create a dynamic table using slicer.

You can use Table as a variable in a measure and that can be dynamic

measure

var _t1 = calculatetable(t1,filter(t1[project]=selectedvalue(table[project]))

Return

countx(_t1,[Col1])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[SteveBrou]

@amitchandak, One of the tables is listed below (it's the same concept for the 3 different scenarios so if I understand one, I should be able to apply to the other 2):
Recruiting Table

Job ID	Job Title	Project	Target Start Date	Recruiting Status	Creation Date
123	Testing Engineer	Project 1	7/5/2020	2	6/1/2020
124	Analyst	Project 2	7/15/2020	0	6/15/2020
125	Developer	Project 2	7/10/2020	2	6/13/2020

I have 3 cards containing the measures to calculate the following: 1. Open Positions, 2. Positions in Progress, and 3. Positions filled. The calculated table that I have for open positions is simple - Recruiting Status =2. This is the filter that tells me the positions in the table that are open. When the user clicks on the card, they go to the table containing information about all the current open positions. My current set up works but I can't use the slicers to look at individual projects or group of projects.

I had to re-read your instructions on the dynamic measure a couple times but I think I may be seeing a little bit of the light on that one. Essentially you are setting up a variable that uses the the calculation for the calculated table instead of the name of the calculated table. How do I display the results in a table. I'm seeing daylight but the sun hasn't risen quite yet 🙂

[v-zhenbw-msft]

Hi @SteveBrou ,

The following is our understanding,

You have three measures that calculate the count of position status, and put them to three card.

When you click one card, for example, you click open positions card, it will jump to a list table that only contains open positions information.

If our understanding is correct, sorry for that there is no perfect solution.

But you can refer this solution as following. Use the button to jump to page, and make a fixed page in advance.

1. Create three pages that contains the Open Positions, the Positions in Progress and Positions filled. We just can use the page filter.

2. Then we can add three buttons below the three cards, to jump to different page.

3. And add a back button in each page.

4. At last, when you want to see the detail of open positions, you can select the button bellow the open positions.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What was the rationale for dividing your data up into separate tables?  Were you ever considering leaving everything in one table and have the status just be a column in that table?

[SteveBrou]

@lbendlin, yes, this is how I orginially wanted to do things. In order for me to filter down to the 3 states of "Open Positions", "Positions In Progress" and "Positions Filled", I couldn't figure out a way to return multiple rows from a table that I could show in a table. I would try to write a measure and get the expecting a scalar result error message. Essentially this dashboard is answering 3 questions:
1. How many open positions do we have now?
2. How many positions have we identified candidates and start dates but the start date is in the future?
3. How many positions have we filled (completed)?
I have this high level information in 3 cards with a blank button on top that when clicked goes to a bookmark showing the table with the correct information detail. In this table, if it shows open positions, it will show the open positions with fields from the calculated table like when the job post was opened, the job title, the project for the job post, etc. Everything works to this point. Now when I want to add filters that the user can set like which project? or for what date range?, the filters do not work on these calculated tables. Th