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,
A question that can either be really easy to answer or too difficult.
I have a database with Orders; Order numbers, product code, date, quantity sold and various other flags (country, product category, e.t.c ). Obviously, one order can have one or multiple order lines (based on the different number of products sold).
I have created a summary table based on the individual order number. In this table, I have the number of lines and the total quantity sold per order. In addition, I have created an output based on the different number of lines counting the number of orders (1 Line Orders are X, 2 Line Orders are Y, e.t.c.)
As I understand that it can be difficult to be understood, I have shared an example of the database
https://www.dropbox.com/s/2715sk921l0iw3r/dynamic%20summarize.pbix?dl=0
Thanks for your time,
Tasos
My problem starts when I apply filters. Let's assume the order 123 has two lines (ProductA and ProductB). Additionally, we have a slicer for the different product categories. When the end user selects the product category 1, product B is excluded. However, the graph, that in X-Axis uses values from the summary table above, still shoes that the order 123 has two lines.
What you need is not summarize, but dimension tables linked to fact table to calculate measures based on different segmentation.
Ex: You'd add dimension tables for ordertype, country, calendar etc.
And then link dimensions to fact table (Orders), using column(s) from dimension table as primary and columns(s) from fact table as foreign. This is typically referred to as Star Schema and one of most common ways to structure data model for reporting/analysis.
You can find articles in links:
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
Oh, I get it now, I misunderstood your question orignally.
To clarify, what you want is dynamic histogram, based on slicer selection?
This isn't possible through conventional means that I can think of.
Since DAX tables and calculated columns are calculated when the model is first processed, and will not update with slicer selection.
Are you ok with using R-Visual with script?
There may be way to do it in DAX, but I'll have to think on it a bit.
Yes. Now you have really understood my problem.
I agree with you, columns are based on the total values and not the page slicers.
I haven't tried the R-visual before. If you can do it, then I can just copy your solution to my file.
In any case, I appreciate your help!
@Tasos i looked at your pbix, not sure why there is a need of summry table. Can you clairfy ?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k, @Chihiro, @LivioLanzo
Thank you all for the immediate reply.
I am not really sure if I do need the summary table or not. The reason why I thought I need it, as I mentioned in my first message, is that the same order can have multiple products/countries etc. What I want to create is a dynamic bar chart where in the X-Axis I am having the different numbers of order lines and the number of orders is used as a value. (in the pbi file, I have added the wanted graph)
I am looking at @Chihiro reply now. Hopefully, my text above helps you understand my original message.
Thanks again for your time
@Tasos it is clear now what you are looking for, there are many way to achieve but going with dimension to link with summarize table is easier way.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Tasos based on your description, you cannot pass on slicer information to summarize table, either you have to have category dimension in the model and set the relationship with calculated summarize table and use that dimension for slicers.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
also as @LivioLanzo mentioned, not sure if you need summary table.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @Tasos
Do you think you really need to create this summary table? I do not think you need it, unless there is something I am missing.
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |