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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
EamonC
Regular Visitor

Count of occurrences of distinct value A for distinct values within a table

Hi, I'm looking for some help with a DAX formula that would count the occurrences of a distinct value A for distinct values within a table. Regards Eamon

 Value A Required Dax formula to return the following values
Distinct ValuesYes/No Number of occurances of yes for each distinct value 
5Yes 1
4Yes 1
4No 1
3Yes 2
3Yes 2
1Yes 2
1Yes 2
1No 2
1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @EamonC 

 

You need a DAX Calculated column?

Vera_33_0-1633692767108.png

Column = 
COUNTROWS(FILTER('Table','Table'[Distinct Values]=EARLIER('Table'[Distinct Values])&&'Table'[Yes/No]="Yes"))

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hey there,

 

I have the exact same problem, BUT I need the formula to create it as a measure instead of calculated column.

Since the EARLIER function as she is applied here is only working for calculated columns and not for measures, I tried following this blog post to translate the formula from the calculated column to a measure formula. Unfortunately without success.
This is my current state:

measure = CALCULATE(
COUNTROWS('Table'),
FILTER(ALL ('Table'), COUNTROWS(FILTER('Table', EARLIER('Table'[Distinct Values])='Table'[Distinct Values]&&'Table'[Yes/No]="Yes")
)
)
)
Every help is greatly appreciated 🙂
Best,
Peter
smpa01
Super User
Super User

Column = CALCULATE(COUNTROWS('fact'),'fact'[Yes/No]="Yes")

 

smpa01_0-1633842612884.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Vera_33
Resident Rockstar
Resident Rockstar

Hi @EamonC 

 

You need a DAX Calculated column?

Vera_33_0-1633692767108.png

Column = 
COUNTROWS(FILTER('Table','Table'[Distinct Values]=EARLIER('Table'[Distinct Values])&&'Table'[Yes/No]="Yes"))

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors