Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a set of data that is being used to track reviews meeting a 30 day deadline:
Asessor | Days since last review |
Asessor 1 | 20 |
Asessor 1 | 34 |
Asessor 1 | 100 |
Asessor 1 | 5 |
Asessor 2 | 19 |
Asessor 2 | 10 |
Asessor 2 | 34 |
Asessor 2 | 28 |
I need to track how many learners are over the 30 days for each asessor, i have tried the below DAX statement but it doesnt seem to work:
Countless than = Calculate ( Count ('Table'[Asessor]), 'Table'[Days Since Last Review] <30
Solved! Go to Solution.
Try:
Less than 30 by row =
CALCULATE(COUNT('Table'[Asessor]), FILTER('Table', 'Table'[Days since last review] < 30))
Over 30 days =
CALCULATE(COUNT('Table'[Asessor]), FILTER('Table', 'Table'[Days since last review] > 30))
Proud to be a Super User!
Paul on Linkedin.
The following measure will return a count fo 1 for every row where the days <30:
Less than = CALCULATE(COUNT('Table'[Asessor]), FILTER('Table', SUM('Table'[Days since last review]) < 30))
However, as you see this works for non - aggregated values; for aggregated values there are no "Asessor"s with a vlue under 30. Also, how do you wish to calculated the total?
Proud to be a Super User!
Paul on Linkedin.
Hi!
Thank you for the swift reply.
Apologies i may have worded that poorly, i need to count how many of the asessors learners havent had a review in over 30 days, asessor 1 in this example has 2 learners over 30 days and asessor 2 has 1 over.
Asessor | Days since last review | Asessor | # over 30 | |
Asessor 1 | 20 | asessor 1 | 2 | |
Asessor 1 | 34 | Asessor 2 | 1 | |
Asessor 1 | 100 | |||
Asessor 1 | 5 | |||
Asessor 2 | 19 | |||
Asessor 2 | 10 | |||
Asessor 2 | 34 | |||
Asessor 2 | 28 |
Try:
Less than 30 by row =
CALCULATE(COUNT('Table'[Asessor]), FILTER('Table', 'Table'[Days since last review] < 30))
Over 30 days =
CALCULATE(COUNT('Table'[Asessor]), FILTER('Table', 'Table'[Days since last review] > 30))
Proud to be a Super User!
Paul on Linkedin.
@georgedeakin51 , Try like
Calculate ( Count ('Table'[Asessor]), filter('Table', 'Table'[Days Since Last Review] <30) )
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |