Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Clint
Helper V
Helper V

Measure Counting incorrectly based on filter

Hello,

 

I have a table of projects that each get an overall Project Score.  This is a measure that adds the results of other measures to calculate the "riskiness" of a project.  Based on the score, each project gets assigned Red/Amber/Green.  What I'm having trouble w/is a measure that counts the "green" projects i.e, those projects that have a score greater than 1.75 and less than 6.  As you can see from the embedded image, the result should be 15 but it is instead returning 13.  

Any insight is appreciated.

Project Score Green =
CALCULATE(
count(BurndownScope[Project Name]),
filter('BurndownScope',
[M_Overall Project Score]>1.75
&&
[M_Overall Project Score]<6
)

)
overall project score no filter.PNG
8 REPLIES 8
v-shex-msft
Community Support
Community Support

Hi @Clint,

I'm not so clear for your table structure, can you please share some dummy data with the same structure? In addition, any filters in your 'M_Overall Project Score' formula? Can you provide this formula for analysis?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft  @JarroVGIT 

Here is the Measure the computes the overall project score:

M_Overall Project Score =
[M_Sched Perf]+[m_Project Issue Score]+[m_Project Risk Score]
+ [M_Scope Score]

 

Here are the Measures that are added in the Overall Project Score:

M_Sched Perf =
1.5*(
DIVIDE
(AVERAGE
('Projects'[Project duration]),
AVERAGE('ProjectBaselines'[Project Baseline Duration])
))
================================================
m_Project Issue Score =
MAXX(
    KEEPFILTERS(VALUES('Issues'[Project Name])),
    CALCULATE(MAX('Issues'[C_Issues Scoring]))
)
================================================
m_Project Risk Score =
MAXX(
    KEEPFILTERS(VALUES('Risks'[Project Name])),
    CALCULATE(MAX('Risks'[c_Risk Scoring]))
)
================================================
M_Scope Score =
Maxx(KEEPFILTERS(VALUES('BurndownScope'[c_Scope Score])),

CALCULATE(
MAXX('BurndownScope','BurndownScope'[c_Scope Score])
))
 
Interestingly, per the attached screenshots, the count doesn't change from 13 regardless whether I filter out cancelled and closed projects (as they should be).  The Project name comes from the burndownscope table and the Project state filter comes from  the "Projects table.  The relationship is a 1 to many from Burndownscope to Projects via a field called Clarity ID.  It is bi-directional
filtersfiltersauto rag tableauto rag table

Hi @Clint,

I found you add some visual level filters, current visual level filter effect only works on current visual. (you can't extract filter effect on other visuals) I'd like to suggest you use these fields on slicer to apply filter effect with other visuals.

In addition, you also use lots of times of keepfilters function in your formulas, I'd like to suggest taking a look at the following blog to know more about its effect and usage.

Using KEEPFILTERS in DAX 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks Xiaoxin, I will take a look.  

Hi @Clint ,

 

I'm just wondering, how are you getting the colored circles, because I would think you could just count the projects where the color is green rather then trying to do the between 2 scores  option?

 

Might not work but thought I'd mention it, sometimes there's many ways to get to the same solution.

Conditional formatting based on the score of the project.

JarroVGIT
Resident Rockstar
Resident Rockstar

Hi @Clint ,

Is it possible to share your PBIX? Unexpected outcomes is very hard to troubleshoot going from one measure and no data. If you don't want to share publicly you can PM me a link and I will have a look tomorrow or so 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @JarroVGIT ,

 

I cannot share the file as it is full of proprietary info but I can share more info on the measure that computes the score, the filters and the table structure.  Xiaoxin Sheng asked for additional info in his reply so I will post there to save redundancy  😉

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.