Forum Discussion
Solved
Count Values with condition
In my data set, I have multiple rows for each 'Name' when each 'Name; has a Skill:Name assigned to them, with a skill level, skill start date and skill end date; The end result I am trying ...
Hi,
Try these calculated column formulas
Count with condition = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Skill End Date]=BLANK()&&Data[Skill Level]<>1))Apportioned count = if(and(Data[Skill End Date]=BLANK(),Data[Skill Level]<>1),DIVIDE(1,Data[Count with condition]),BLANK())Hope this helps.
Thank you! This work perfectly!!
Hi,
So For Calvin Lip, you want to see 0.25 in another column (in each of the 4 rows) and for Kevin, you want to see null, 0.5, 0.5 and null. Am i correct? Share the download link of the PBI file.
Yes, that is correct. Here is a link to a sample file.
Hi,
Try these calculated column formulas
Count with condition = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Skill End Date]=BLANK()&&Data[Skill Level]<>1))Apportioned count = if(and(Data[Skill End Date]=BLANK(),Data[Skill Level]<>1),DIVIDE(1,Data[Count with condition]),BLANK())Hope this helps.
Thank you! This work perfectly!!
You are welcome.