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.
Hello,
I have a table like this:
City | Fiscal Period | User | Value | Col5 | Col6 |
A | 01-17 | User1 | 100 | ... | ... |
A | 02-17 | User1 | 120 | ... | ... |
A | 01-17 | User1 | 100 | ... | ... |
B | 03-17 | User1 | 200 | ... | ... |
A | 02-17 | User3 | 100 | ... | ... |
B | 04-17 | User2 | 300 | ... | ... |
And I would like to determine which rows are showing the following combinations:
Is it possible to achieve this with DAX, or shall I use the PowerQuery editor only?
Thanks!
Fred
Solved! Go to Solution.
The Group By will look for a grouping of all 4 fields. So it's like if CIty A, Period1, User A, Value 1 then count +1.
If the user is different, then that would be a different group.
Here is my inital mock up dataset.
I did the group by like this:
The result dataset is like this:
Just looking at what you are requesting...I would suggest to use the query editor. It seems like you want to "group by" city, same period, same user, same value. To do this, go to edit queries. Click Group By. Advanced. Include the different groupings in the top section. The bottom section is probably just a count rows of those groupings (if you have a sum or other aggregate, try that instead of count rows).
I appreciate your swift response!
But how can I identify cases such as "same city, different period, same user, same value"?
Say I have the following scenario:
city A, period 1, user B, value C
city A, period 1, user B, value C
city A, period 2, user B, value C
At the end, I would like to have this in my resulting table:
city A, period 1, user B, value C, count 2
city A, period 2, user B, value C, count 1
I understand I'd have to group by city, user and value, but how can I ensure I won't have cases with the same period appearing more than once?
Thanks.
The Group By will look for a grouping of all 4 fields. So it's like if CIty A, Period1, User A, Value 1 then count +1.
If the user is different, then that would be a different group.
Here is my inital mock up dataset.
I did the group by like this:
The result dataset is like this:
Thank you!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |