Count members based on Measure result

[Aruljoy]

Hi,

I want to count the no of members who has FailOnlyFlag=1. FailonlyFlag is a measure basen columns measure_Successcount & Measure_failcount

FailOnlyFlag = if([Measure_Successcount]=0 && [Measure_Failcount] > 0 , "1", "0")

Can you please let me know how to get the count of members whos flag is 1?

[laurenN]

@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

[TheoC]

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 🙂

[laurenN]

@TheoC Thank you so much for getting back to me - I managed to somehow find a solution yesterday in the end - perhaps not as elegant as it could be but it does the job - I used this:

and it's giving me the desired results.  Thank you so much for your time anyway though Theo, I appreciate it

[TheoC]

@laurenN  love it! Well done on getting a solution! 

[sevenhills]

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

[Aruljoy]

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)

[sevenhills]

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]

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

[Aruljoy]

Thanks @TheoC  for the reply. But still there no count in the total

[TheoC]

Hi @Aruljoy did you do it as a measure or a column?

[Aruljoy]

its a measure

[TheoC]

@Aruljoy try this:

Measure = 

VAR _1 = COUNTROWS ( FILTER ( VALUES ( 'Table'[MemID] ) , [Measure_SuccessCount] > 0 ) )
VAR _2 = IF ( _1 > 0 , COUNTROWS ( FILTER ( VALUES ( 'Table'[MemID] ) , [Measure_SuccessCount] > 0 ) ) , 0 )

RETURN

_2

You can also use ISBLANK to replace the VAR _2 but it's much of a muchness.

Measure = 

VAR _1 = COUNTROWS ( FILTER ( VALUES ( 'Table'[MemID] ) , [Measure_SuccessCount] > 0 ) )
VAR _2 = IF ( ISBLANK ( _1 ) , 0 , _1 )

RETURN

_2

Also, just below is a screenshot of the output:

Let me know how it goes @Aruljoy 

Cheers,

Theo

[Aruljoy]

I tried the above DAX query for my logic, still count is not matching

[TheoC]

Hi @Aruljoy please use the measure as it's written.  You need to use > and not the = sign.

Measure = 

VAR _1 = COUNTROWS ( FILTER ( VALUES ( 'Table'[MemID] ) , [Measure_SuccessCount] > 0 ) )
VAR _2 = IF ( _1 > 0 , COUNTROWS ( FILTER ( VALUES ( 'Table'[MemID] ) , [Measure_SuccessCount] > 0 ) ) , 0 )

RETURN

_2

  Your output is wrong because of the = sign per below:

Once this is corrected, the output will be correct.

Thanks,

Theo

[TheoC · Accepted solution]

@Aruljoy you used the wrong measure.  You have = 0 instead of > 0. Please fix your measure and try again.

[Aruljoy]

Thanks @TheoC . This works for me..

[TheoC]

@Aruljoy no trouble at all! All the best 👍