Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |