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.
Hey everbody,
I would like to create a column that shows the occurance of a purchase on a specific adress and date. In other words, i would like to perform a distinct count of a combined Adress & date in the column Group Total.
Usually, i would use a DISTINCT COUNT function for the combination of a DATE & Adress combined with an ALLEXCEPT statement for this combination. However, in this specific case I require the other columns to be empty.
Adress of individual | Date of purchase | Product Group | Group Total |
Homestreet 1 | 1-1-2021 | A | 1 |
Homestreet 1 | 1-1-2021 | B | |
Homestreet 1 | 4-2-2021 | A | 1 |
Teststreet 2 | 1-2-2021 | A | 1 |
Teststreet 2 | 1-2-2021 | C | |
Teststreet 3 | 1-2-2021 | C | 1 |
Is there anyway to perform this action in Powerbi using DAX or Powerquery?
Best regards and thanks in advance,
Ivan Pettinga
Solved! Go to Solution.
Hi @Ivan274 ,
You could add an index column. Use MAX() and ALLEXCEPT() to get the max index for each group. Then if index equal to max index, then group total, else blank.
Best Regards,
Jay
Thank you for your answer, i managed to get it working based on your feedback!
Hi @Ivan274 ,
You could add an index column. Use MAX() and ALLEXCEPT() to get the max index for each group. Then if index equal to max index, then group total, else blank.
Best Regards,
Jay
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |