Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am stuck into this I had this table to which I converted to 2nd table below.
User | Date | Hours Spent |
Arun Gopinath Prabhu | 27/07/2021 | 2.5 |
Fahad Rauf Shaikh | 28/07/2021 | 3.6 |
Vikrant Sanjay Tamboli | 27/07/2021 | 1.4 |
Vikrant Sanjay Tamboli | 28/07/2021 | 1.1 |
Arun Gopinath Prabhu | 28/07/2021 | 3.1 |
Rajtarangini | 28/07/2021 | 1.8 |
Swati Singh | 27/07/2021 | 2.3 |
Utkarsha Sharma | 28/07/2021 | 4.5 |
Utkarsha Sharma | 27/07/2021 | 1 |
Shubham A Tembhurnikar | 27/07/2021 | 1.4 |
Shubham A Tembhurnikar | 28/07/2021 | 1.1 |
Rajtarangini | 27/07/2021 | 3.1 |
Swati Singh | 28/07/2021 | 0.4 |
Fahad Rauf Shaikh | 27/07/2021 | 2.2 |
I converted above table to the table below.
Users | Baseline Hours | Total Hours |
Arun Gopinath Prabhu | 5 | 5.6 |
Fahad Rauf Shaikh | 5 | 5.8 |
Vikrant Sanjay Tamboli | 5 | 2.5 |
Rajtarangini | 5 | 4.9 |
Swati Singh | 5 | 2.7 |
Utkarsha Sharma | 5 | 5.5 |
Shubham A Tembhurnikar | 5 | 2.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.
Users | Baseline Hours | Total Hours | Desired Outcome Count |
Arun Gopinath Prabhu | 5 | 5.6 | 1 |
Fahad Rauf Shaikh | 5 | 5.8 | 1 |
Vikrant Sanjay Tamboli | 5 | 2.5 | 0 |
Rajtarangini | 5 | 4.9 | 0 |
Swati Singh | 5 | 2.7 | 0 |
Utkarsha Sharma | 5 | 5.5 | 1 |
Shubham A Tembhurnikar | 5 | 2.5 | 0 |
Solved! Go to Solution.
@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))
@Hamdan1234
The below query helps you to make a column with the result.
If(Table[HoursTotal]>5,1,0)
Proud to be a 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✌️!!
@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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |