cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trabass Frequent Visitor
Frequent Visitor

Visual Filters vs Measures on a table

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 

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.

 

2017-11-02_13-15-24.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
trabass Frequent Visitor
Frequent Visitor

Re: Visual Filters vs Measures on a table

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?

View solution in original post

3 REPLIES 3
Microsoft v-jiascu-msft
Microsoft

Re: Visual Filters vs Measures on a table

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trabass Frequent Visitor
Frequent Visitor

Re: Visual Filters vs Measures on a table

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?

View solution in original post

trabass Frequent Visitor
Frequent Visitor

Re: Visual Filters vs Measures on a table

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

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,465)