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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hamdan1234
Helper III
Helper III

Dax Count function with sum

I am stuck into this I had this table to which I converted to 2nd table below.

UserDateHours Spent
Arun Gopinath Prabhu27/07/20212.5
Fahad Rauf Shaikh28/07/20213.6
Vikrant Sanjay Tamboli27/07/20211.4
Vikrant Sanjay Tamboli28/07/20211.1
Arun Gopinath Prabhu28/07/20213.1
Rajtarangini28/07/20211.8
Swati Singh27/07/20212.3
Utkarsha Sharma28/07/20214.5
Utkarsha Sharma27/07/20211
Shubham A Tembhurnikar27/07/20211.4
Shubham A Tembhurnikar28/07/20211.1
Rajtarangini27/07/20213.1
Swati Singh28/07/20210.4
Fahad Rauf Shaikh27/07/20212.2

I converted above table to the table below.

UsersBaseline HoursTotal Hours
Arun Gopinath Prabhu55.6
Fahad Rauf Shaikh55.8
Vikrant Sanjay Tamboli52.5
Rajtarangini54.9
Swati Singh52.7
Utkarsha Sharma55.5
Shubham A Tembhurnikar52.5

I summed up the hours duration to respective user but now I want to count the number of user based on total hours above 5 so my outcome should be below table showing the count of over achiever which exceeds baseline of 5 hours. 

UsersBaseline HoursTotal HoursDesired Outcome Count
Arun Gopinath Prabhu55.61
Fahad Rauf Shaikh55.81
Vikrant Sanjay Tamboli52.50
Rajtarangini54.90
Swati Singh52.70
Utkarsha Sharma55.51
Shubham A Tembhurnikar52.50

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Hamdan1234 , Assuming Total Hours is a measure. Try a measure like

 

Countx( filter(Values(Table[Users]), [Total Hours] >5), [USers])

 

or

 

sumx( Values(Table[Users]),if( [Total Hours] >5, 1, 0))

View solution in original post

4 REPLIES 4
manikumar34
Solution Sage
Solution Sage

@Hamdan1234 
The below query helps you to make a column with the result.
If(Table[HoursTotal]>5,1,0)





If this helps, Appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




VahidDM
Super User
Super User

Hi @Hamdan1234 

You can add your desired column to the converted table with this code:

If(table[Total Hours ]>5,1,0)

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!



 

amitchandak
Super User
Super User

@Hamdan1234 , Assuming Total Hours is a measure. Try a measure like

 

Countx( filter(Values(Table[Users]), [Total Hours] >5), [USers])

 

or

 

sumx( Values(Table[Users]),if( [Total Hours] >5, 1, 0))

Instead of putting fixed number like 5  can we use a measure to make it dynamic?How can we do it like how to create it ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.