Forum Discussion
Solved
Flag a user based on few data points
I am creating a power bi report based on monthly survey data. One of the question is productivity gain. I want to identify users based on following criteria:
- Number of survey taken is greater than 2
- reported an upward or consistent number on Productivity gained (look at the sample data)
| Survey Date | User | productivity |
| 1/1/2023 | A | 30 |
| 2/1/2023 | A | 30 |
| 3/1/2023 | A | 45 |
| 4/1/2023 | A | 33 |
| 1/1/2023 | B | 30 |
| 2/1/2023 | B | 35 |
| 1/1/2023 | C | 20 |
| 2/1/2023 | C | 30 |
| 3/1/2023 | C | 40 |
| 1/1/2023 | D | 45 |
| 2/1/2023 | D | 45 |
| 4/1/2023 | D | 45 |
Expected Output
| User | Flag Super User |
| A | N |
| B | N |
| C | Y |
| D | Y |
I tried couple of DAX formula's but the output isn't what is expected in the above sample data.
Hi,
PBI file attached.
Hope this helps.
5 Replies
Hello PoojaG,
Can you please try this:
1. Count Surveys per User
Survey Count = COUNTROWS('Survey')2. Flag Users Based on Criteria
Flag Super User = VAR CurrentUser = 'Survey'[User] VAR SurveyCount = CALCULATE(COUNTROWS('Survey'), FILTER('Survey', 'Survey'[User] = CurrentUser)) VAR FirstProductivity = CALCULATE(MIN('Survey'[productivity]), FILTER('Survey', 'Survey'[User] = CurrentUser), ALL('Survey')) VAR LastProductivity = CALCULATE(MAX('Survey'[productivity]), FILTER('Survey', 'Survey'[User] = CurrentUser), ALL('Survey')) RETURN IF(SurveyCount > 2 && LastProductivity >= FirstProductivity, "Y", "N")Hope this helps.
this solution may not consider all the in between survey results. only the first and the last.
Hi,
PBI file attached.
Hope this helps.
this works perfectly. thank you so much. I appreciate your help
You are welcome.
