Measure to identify if a value exists in a list?

[Anonymous]

Hi all

I am building dashboards to assist with project managagement in my organisation.

At a high level, this consists of a couple of queries - one for our project budget, showing what employees will work on what tasks and for how many hours each week, and a query showing our time sheet data, showing the actual hours spent on each task per week.

I would like a measure that highlights when individuals have charged time to the project who are not included in that project's budget.

Both queries are structured similiarly, with fields such as [employee name] [employee ID] [sub-project] [hours] [week ending date] etc

The measure would be used to create a table visual. 

I was thinking some kind of array would be helpful - it should create a virtual list of employees that are meant to be working on a particular week per the budget - and if there are any employees listed in the timesheet data, to only show their names.

thanks

[PaulDBrown]

@Anonymous 

Here is one way of doing it.

First the model:

Add a new column to your budget table and Time sheet table: concatenate the values for Employee ID & Project & Sub-project

Budget TableTimeSheet Table

Now create measures to identify which activities were not budgeted:

Non-Budgeted Activities = 
VAR _Budget = VALUES(Budget[BudgetActivity ID])
VAR TSHEET = VALUES(TimeSheet[TimeSheet Activity ID])
RETURN
COUNTROWS(
    EXCEPT(TSHEET, _Budget))

Non-Budgeted Hours = 
VAR _Budget = VALUES(Budget[BudgetActivity ID])
VAR TSHEET = VALUES(TimeSheet[TimeSheet Activity ID])
RETURN
SUMX(EXCEPT(TSHEET, _Budget), [Sum TimeSheet Hours])

And you get this:

PS: if you prefer to avoid having to include new columns and use only measures, you can follow this structure for the measures:

Non-Budgeted by measure = 
VAR BudgActID = SELECTCOLUMNS(
             SUMMARIZE(Budget, Budget[Employee ID], Budget[Project], Budget[Sub-project], "PID", 
             Budget[Employee ID] & Budget[Project] & Budget[Sub-project]),
             "PID", [PID])
VAR TSHEETActID = SELECTCOLUMNS(
             SUMMARIZE(TimeSheet, TimeSheet[Employee ID], TimeSheet[Project], TimeSheet[Sub- 
             project], "PID", TimeSheet[Employee ID] & TimeSheet[Project] & TimeSheet[Sub-project]), 
             "PID", [PID])
RETURN
COUNTROWS(EXCEPT(TSHEETActID, BudgActID))

[Ashish_Mathur]

Hi,

Share some data and show the expected results.

[lbendlin]

ok, what have you tried so far?  Are you familiar with the concept of joins, "show items with no data", and disconnected tables?

[Anonymous]

HI 

I have considered using CALCULATE and summing hours in my time-sheet table, filtered by the current week ending and where the budget hours = 0 for that week, but I hoped there might be a more elegant solution that would boost my DAX ability.

I am familiar with the concepts you list but i don't know how they could apply to my situation.

The data is sensitive therefore I don't want to give an example - it would be nice if someone could provide examples at a conceptual level for now.