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.
Hi,
I have a requirement in my Power BI report that I'm struggling with for days now.
My datamodel is quite large with > 20 tables. I would like a matrix where I can count the distinct amount of customers and let the user of the report in his browser dynamically add and remove dimensions as rows and/or columns in where the distinct amount of customers is grouped by the values of these dimensions.
In short: I’m searching for a way to create the Excel Pivot Table functionality J. The goal is that the user of the report can create their own custom made reports in his browser in which the value is always the distinct amount of customers, but the rows and columns in which those amount of customers are grouped can manually be selected.
Simple Example (with only one table)
ID | Office | Active | Color |
1 | GR | Yes | Red |
2 | GR | Yes | Blue |
3 | GR | No | Blue |
4 | NO | No | Blue |
5 | NO | No | Red |
6 | NO | No | Red |
7 | ZW | No | Red |
8 | ZW | No | Blue |
9 | ZW | Yes | Blue |
10 | ZW | Yes | Blue |
The User can choose the report rows and columns. For example he wishes to see the amount of customers by Color and Office as Rows and Active as a Column. The result should something like this:
Rows | Yes | No | Total |
Blue | 3 | 3 | 6 |
GR | 1 | 1 | 2 |
NO | 1 | 1 | |
ZW | 2 | 1 | 3 |
Red | 1 | 3 | 4 |
GR | 1 | 1 | |
NO | 2 | 2 | |
ZW | 1 | 1 | |
Total | 4 | 6 | 10 |
Thanks & Regards,
Tiemen
Solved! Go to Solution.
The new preview feature 'personalize visual experience' is the solution for me. Thank you for your help.
Regards,
Tiemen
The new preview feature 'personalize visual experience' is the solution for me. Thank you for your help.
Regards,
Tiemen
Hi @Anonymous ,
Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.
Best Regards,
Yingjie Li
Hi @Anonymous ,
Based on your expected output, you can use Matrix visual to achieve it directly:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , use all expect
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
This should give you the required data
Hi @amitchandak ,
Thanks for your reply. Personally I do not the relation between creating a customizable pivot table in Power BI for browser based users and using ALLEXCEPT in DAX. Can you explain how ALLEXCEPT can help me in creating the customizable pivot table?
Regards,
Tiemen
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |