Help with DAX measure

[smstrickland]

My organization has two time entry systems. I have created a matrix that shows the total number of hours entered into each system, per project, that can be filtered by employee and pay period. A sample result is below, with the Comparison column being a measure that subtracts the time in System 2 from the time in System 1. (Project Name, System 1 time, and System 2 time are all in different tables connected through a series of relationships.)

	Pay Period Ending 1/31/2020
Project Name	Syst 1 Time	Sys 2 Time	Comparison
PROJ 1	5.5	5.5	0
PROJ 2	5.5	5.5	0
PROJ 3	12.25	12.25	0
PrROJ 4	13.75	13.75	0
PROJ 5	0.5	0.5	0
PROJ 6	3.5	3.5	0
PROJ 7	18	18	0
PROJ 8	0.5		-0.5
PROJ 9	7.5	8	0.5
PROJ 10	5	5	0
PROJ 11	3	3	0
Total	75	75	0

In another matrix, I want to show, by employee, whether or not the time entered into both systems match. I have been able to create a measure that will compare the total hours in each system, filtered by pay period, but that result is not what I want since, as can be seen in the above sample data, the total number of hours in each system does match, but the number of hours per project does not. I want to be able to show that this person's time does not match in both systems without having to include the project rows in the second matrix.

Is there a way to do this? I have thought that I might need to use 'group by' but I just don't know enough about DAX to know if that is correct or how to write such a measure if it is. Any help would be appreciated.  --Shauna

BTW - I am not able to provide .pbix files due to company policy, but I'm happy to provide additional information about the data sets if needed.

[Greg_Deckler]

Well, it is very difficult to be specific because you talk about a person and wanting to display by person but there is no person in the sample data and no information about if the person name is in the table or another related table, how those tables are related, etc.

So, I can provide a general solution. Use SUMMARIZE or GROUPBY to summarize by person and by project and add in your Comparison measure/column. Filter that such that you filter out any 0's for Comparison. Do a COUNTROWS. If COUNTROWS is 0/BLANK then no discrepencies. Otherwise, discrepencies.

[smstrickland]

@Greg_Deckler Sorry about the confusion/vagueness within my first post. I have a table that contains Projects, a table that contains Employees, a table that contains time from System 1, a table that contains time for System 2, and a table that contains the Pay Period Dates as well as a number of other tables that provide additional information that help to create all of the relationships between the tables. Employee is not included in the sample data matrix because it is not a field in that matrix. Instead, I have used a slicer to allow the user to filter by Employee.

I hope that helps.  --Shauna

[Greg_Deckler · Accepted solution]

In that case, then somthing like this should work:

Measure =
  VAR __Table =
    SUMMARIZE(
      'Table',[Project],
      "__Comp",[Comparison]
    )
RETURN
  IF(COUNTROWS(FILTER(__Table,[Comparison] <> 0)) = 0,"Good","Bad")

[smstrickland]

@Greg_Deckler Thank you so much! This worked (and I marked it as a solution), but I would prefer for blanks to be considered "Bad." How should I change the IF statement to accomplish that?

[Greg_Deckler]

@smstrickland - Great! Not sure I follow though on your follow-up question.

Measure =
  VAR __Table =
    SUMMARIZE(
      'Table',[Project],
      "__Comp",[Comparison]
    )
RETURN
  IF(COUNTROWS(FILTER(__Table,[Comparison] <> 0)) = 0,"Bad","Good")

?

[smstrickland]

@Greg_Deckler Never mind the last question, and thank you again for your help.  --Shauna

[Greg_Deckler]

No problem! 🙂