I have multiple cascading parameters in my report.. how do we cascade two dimensions from different tables in paginted report.. my source dataset is Power Bi Desktop and there is no direct connection between dimensions which i need to cascade
Thank you for this interesting video that I watched while looking for a solution to a problem I am currently trying to solve. The dataset in your example is perfectly suitable to build upon. Suppose you add a numeric field 'CreditLimit' and fill it with random values. Then, you could create a paginated report that displays the following for each customer: Column 1: Customer Column 2: CreditLimit Column 3: OrderDateKey Column 4: SalesAmount The report should show 2 grand totals: (1) Total of individual CreditLimits (2) Total of SalesAmounts The (1) total of individual CreditLimits seems, based on the methods I have tried so far, not to lead to a correct result. This might be somehow related to the topic you covered in your video. How would you approach this?
Hi there, Thanks for the feedback. I guess in the example you gave, CreditLimit is getting accounted for each date. If example, if credit limit for customer A is $100 and you are seeing the same by date for 5 days, I guess you are getting sum of $500 for the credit limit, is this correct understanding? Let me know and I will give the solution accordingly. Thanks for watching the video and visiting the channel.
@@PowerBIHowTo Hi, thanks for your reply. Well, actually the CreditLimit for a customer should be considered as fixed. It doesn't change so often. Let's say that customer A has a CreditLimit of 100,000 USD, customer B has a CreditLimit of 50,000 USD, customer C has a CreditLimit of 15,000 USD and let's presume we recorded following transactions: Customer A: 12/02/2023 13,000 USD Customer A: 13/04/2023 2,000 USD Customer A: 17/08/2023 20,000 USD Customer B: 15/06/2023 35,000 USD Customer B: 17/08/2023 20,000 USD (no transactions for Customer C) My paginated report should need to show two grand totals and the end of the report: (1) Total of CreditLimits: 150,000 USD (the sum of the individual credit limits for customers with recorded transactions, in this case 1000,000 for A + 50,000 USD for B) (2) Total of SalesAmounts: 90,000 USD In most of my attempts (scenario's) I tried out in paginated reports I would ('''wrongly''') end up with a Total of CreditLimits of 400,000 USD. But it should be 150,000 USD instead. So hope you can help me on this ;)
I am using one multivalued parameter and one year and another is Quarter Parameter. My Paginated report is working fine but Once I deploy the report in Power BI services and try to preview- It takes too much time to fetch the data and every time 700k records are fetching and giving the expected result. Do I know how to stop fetching all the records and filter only expected data from the dataset. ( Year and Quarter filters are coming from the custom table which is part of the max date and min date from one of the data tables but does not make any relationship with the fact table)
Hi there, Thanks for your message. I see the reason why you are getting all the rows because of no relationship. There are two ways to solve it, create a relationship (I guess you are not able to do so), and 2nd is to update your DAX query in the paginated report to filer on selected parameter values. Check this video where DAX query was changed based on the parameter, you can extend this solution into your report. th-cam.com/video/An9we_H_-o8/w-d-xo.html
I have multiple cascading parameters in my report.. how do we cascade two dimensions from different tables in paginted report.. my source dataset is Power Bi Desktop and there is no direct connection between dimensions which i need to cascade
Check this video and let me know if still have questions. Thanks!
th-cam.com/video/nkR0VU8-euw/w-d-xo.html
I am using your ideas of utilizing measures as part of the dataset and my reports run so much faster now! Thank you so much for your videos!
This is a great news. Glad it was helpful. Appreciate the feedback.
Thanks this is soooo helpful!!!
Glad it was helpful! Thanks for the feedback. Much appreciated!
Thank you for this interesting video that I watched while looking for a solution to a problem I am currently trying to solve. The dataset in your example is perfectly suitable to build upon. Suppose you add a numeric field 'CreditLimit' and fill it with random values. Then, you could create a paginated report that displays the following for each customer:
Column 1: Customer
Column 2: CreditLimit
Column 3: OrderDateKey
Column 4: SalesAmount
The report should show 2 grand totals:
(1) Total of individual CreditLimits
(2) Total of SalesAmounts
The (1) total of individual CreditLimits seems, based on the methods I have tried so far, not to lead to a correct result. This might be somehow related to the topic you covered in your video.
How would you approach this?
Hi there, Thanks for the feedback. I guess in the example you gave, CreditLimit is getting accounted for each date. If example, if credit limit for customer A is $100 and you are seeing the same by date for 5 days, I guess you are getting sum of $500 for the credit limit, is this correct understanding? Let me know and I will give the solution accordingly.
Thanks for watching the video and visiting the channel.
@@PowerBIHowTo Hi, thanks for your reply. Well, actually the CreditLimit for a customer should be considered as fixed. It doesn't change so often.
Let's say that customer A has a CreditLimit of 100,000 USD, customer B has a CreditLimit of 50,000 USD, customer C has a CreditLimit of 15,000 USD and let's presume we recorded following transactions:
Customer A: 12/02/2023 13,000 USD
Customer A: 13/04/2023 2,000 USD
Customer A: 17/08/2023 20,000 USD
Customer B: 15/06/2023 35,000 USD
Customer B: 17/08/2023 20,000 USD
(no transactions for Customer C)
My paginated report should need to show two grand totals and the end of the report:
(1) Total of CreditLimits: 150,000 USD (the sum of the individual credit limits for customers with recorded transactions, in this case 1000,000 for A + 50,000 USD for B)
(2) Total of SalesAmounts: 90,000 USD
In most of my attempts (scenario's) I tried out in paginated reports I would ('''wrongly''') end up with a Total of CreditLimits of 400,000 USD. But it should be 150,000 USD instead.
So hope you can help me on this ;)
I am using one multivalued parameter and one year and another is Quarter Parameter. My Paginated report is working fine but Once I deploy the report in Power BI services and try to preview- It takes too much time to fetch the data and every time 700k records are fetching and giving the expected result. Do I know how to stop fetching all the records and filter only expected data from the dataset. ( Year and Quarter filters are coming from the custom table which is part of the max date and min date from one of the data tables but does not make any relationship with the fact table)
Hi there, Thanks for your message. I see the reason why you are getting all the rows because of no relationship. There are two ways to solve it, create a relationship (I guess you are not able to do so), and 2nd is to update your DAX query in the paginated report to filer on selected parameter values.
Check this video where DAX query was changed based on the parameter, you can extend this solution into your report.
th-cam.com/video/An9we_H_-o8/w-d-xo.html