Forum Discussion
Count members based on Measure result
Hi,
I want to count the no of members who has FailOnlyFlag=1. FailonlyFlag is a measure basen columns measure_Successcount & Measure_failcount
Can you please let me know how to get the count of members whos flag is 1?
17 Replies
Hi Aruljoy
Just remove the quotations around the 1. By using "1" instead of 1, Power BI is taking it that you want text instead of a value. Make sure to do the same for 0. You may need to ensure the format is set to Whole Number too in the top ribbon.
Hopefully this gets you the result you're after. If not, let me know 🙂
Hope this helps.
Theo
Fail Only Flag = var _a = COUNTROWS( Filter( 'Table', if ( 'Table'[Measure_SuccesCount] = 0 && 'Table'[Measure_FailCount] > 0, 1, 0))) RETURN If (ISBLANK(_a), 0, _a)Try this
Measure_Successsount & Measure_failcount are also measures. Eg, The first row should return as 0, but I am getting 36.
Measure_Successcount =Var Result = CALCULATE(countrows(TransactionData),FILTER(TransactionData,TransactionData[TransactionTypeDerived]="success"))Return if(isblank(Result)=TRUE(),0,result)Measure_Failcount =--Var StarDate = MIN(DimDate[TransactionDate])--Var EndDate = max(DimDate[TransactionDate])Var Result = CALCULATE(countrows(TransactionData),FILTER(TransactionData,TransactionData[TransactionTypeDerived]="fail"))Return if(isblank(Result)=TRUE(),0,result)In your original post, you said as "....measure basen columns"
DAX behave differently for measures vs columns.
Try this:
Fail Only Flag 2 = var _a = if ([Measure_FailCount] > 0 && [Measure_SuccesCount] = 0, 1, 0) var _t1 = SUMMARIZE('Table','Table'[MemID], "__Value1", if ([Measure_FailCount] > 0 && [Measure_SuccesCount] = 0, 1, 0) ) RETURN If( HASONEVALUE('Table'[MemID]), _a, Sumx(_t1, [__Value1]))
TheoC Hello! I have the same issue but in a different scenario and I've tried to steal the solution you have created for Aruljoy but it's not working for me so I'm clearly missing something - please could you help?!
I have a table with date of update, project reference number and current RAG status - this pulls into my report as "RAG Outcome" column and contains either "RED", "AMBER" or "GREEN".
In order to build a measure to return whatever the most recent RAG is for any given project - so only one result per project but that result could be March's update, or June's update etc etc. So to do that I used LASTNONBLANKVALUE, which needed a measure, not a column value so have ended up with...
Then I have the measure which gives me the latest RAGs only - which works for me
And have another measure to convert those names into RAG colour numbers for easier conditional formatting of other tables..
I tried to play around with your solution above but it's not giving me what I need...
I'm after three separate measures which just count how many GREEN/AMBER/RED rags there are in the latest RAG values measure/output. All my attempts have either added up all of them regardless of date/being most recent or not done anything. I'd be so grateful if you could make sense of this for me - I've spent hours on it already and going stir crazy 🙂
Thank you
- Lauren
Hi laurenN
Apologies for the late reply. I am not sure the two issues align quite as would be required to give you the solution that you're after.
Given this is a closed post, can you do me a massive favour and add a new post? Also, can you just provide a sample of the data that you are dealing with so that I (or another member of our Community) can better understand it and provide a solution efficiently?
Thanks heaps and please feel free to tag me in the post or in a comment once you've made the initial post.
Theo 🙂
