Forum Discussion
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 to get to is to find out how many 'names' we have for each Skill:Name in the data set. In the above example, 'Calvin Lip' has 3 skills (Costco, Reface Sales Appt, Costco Bath - Sales Appt, and Bath Solutions Sales Appt). Becuase he has 4 skills, he would count as .25 in each (Count of Skill:Name by Name / 1).
The other caveat to the formula, is I only want to include the Skill:Name where the end date is NULL (IE: the skill is active) and where the skill level is NOT 1, as in this data set example;
For Kevin, he would count as .50 in each (not counting the skill with an end date nor the skill level of 1.
Any suggestions are appreciated!
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!!
5 Replies
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.