Visual Filters vs Measures on a table

[trabass]

Hi There,  Struggling to figure out a filter, sure it's simple but I'm stuck. 

Currently I have three visual filters setup on dashboard - company, financial year, quarter.

For example.  If I change financial years or quarters on a dashboard the figures for each row in the table add up correctly with the visual filters.

Company = A

Quarter = 1

Year = 2017

Project Name	Target	Actual
Project A	10	10
Project B	20	10
Project C	0	10

The table behind this consists for mulitple rows divided into months. i.e.

Tasks
Project Name	Company	Target	Actual	Month	Financial Year	Quarter
Project A	A	2	2	7	2017	1
Project A	A	2	2	8	2017	1
Project A	A	6	6	9	2017	1
Project B	B	20	10	9	2017	1

My problem is I need to calculate a status for tasks based on the summing a number of tasks, depending on the visual filters selected (i.e. everything for company A, 2017 and Quarter 1), which kind of works.

 Created a measure in the Tasks table called Status. 

Actual formula as follows which adds up numbers in another two columns.

Status = IF (AND(sum(Tasks[BenefitTarget1]) = 0, sum(Tasks[BenefitActual1]) = 0), "No Benefits Identified",

IF (AND(sum(Tasks[BenefitTarget1]) > 0, sum(Tasks[BenefitActual1]) = 0), "Not Started",

IF (AND(sum(Tasks[BenefitTarget1]) = 0, sum(Tasks[BenefitActual1]) > 0), "No Benefit Target Set",

IF (AND(sum(Tasks[BenefitTarget1]) > 0, sum(Tasks[BenefitActual1]) < sum(Tasks[BenefitTarget1])), "Not Yet Realised", "Benefit Targets Realised"))))

As I said this kind of works, except for some reason it also returning other companies in the table still on the dashboard…. The status works out correctly.  If I remove the measure the table filters perfectly.

My data model looks like this.

Any advice? Sorry for the long post but trying to explain.

[v-jiascu-msft]

Hi @trabass,

Your model is clear and simple. It looks great just from your description. I have a few questions.

1. Are Tasks[BenefitTarget1] and Tasks[BenefitActual1] measures or columns?

2. Where are the fields of the slicers from? Are they from the three 1-side data tables?

3. How did you make the visual table? Some fields should from the 1-side data tables.

Could you please share a dummy pbix file?

Best Regards!

Dale

[trabass · Accepted solution]

Hi Dale,

Thanks for the response.  I'll put together a dummy pbix file and add it.  Would like to learn how to make this work.

1. Are Tasks[BenefitTarget1] and Tasks[BenefitActual1] measures or columns?  > These are columns of data

2. Where are the fields of the slicers from? Are they from the three 1-side data tables? > Yes.  I created them so i had unique key to filter between the tables.

3. How did you make the visual table? Some fields should from the 1-side data tables.  > No, i didn't know this.  The table on the dashboard is just off the tasks table.  I will change it.

Could you please share a dummy pbix file?

[trabass]

Thanks for your help.  Yes adding in the 1-sided fields would resolve the problem.