Note for anyone who might be stuck, you need to have both relationships created, even if the one is inactive. Unsure if I missed this being stated in the video or if it was just inferred. Regardless, thanks for the video, it worked!
You've taught me so much in PowerBI over the years. I've been watching your videos since 2019. This issue of multiple dates connecting to a dim date has been an issue for me for ages. I finally took the time to learn how to figure out a way around it. Most of my datasets have 2-3 dates in them to my one DimDate column. Thanks so much for posting this video.
Hi @rosswilliams1058, we’re glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!
Thanks for the video. Very well laid out. Have you or anyone else figured out a way to do this with one measure? It would be nice to have a dynamic way to switch between dates with one measure.
Nice Video - Question if you wanted to have a table list showing two date columns, one is Ordered date, and one is delivered date, how would you solve that issue?
Hi @samhall100, we’re glad you found this video helpful! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!
Thank you for this video! The fact that power bi makes you write separate measures for each really feels like a failure of power bi. It obviously CAN have separate connections, it just doesn't want to unless you force it in DAX?
Hey Sam - this one super helpful and yes, it has come up a few times. This is very clear so thanks. I do have a usage question though: I'm rarely using just the one (date-reliant) calculation in the one report (eg: other financial info like GM$, GM% etc over some time period like FY or Month etc). It is worth mentioning that we also have other separate reports of financial information (measures and multiple visuals), based on the PRIMARY date relationship to the same fact table. So would it be best practice to come up with a separate formula/measure (using the secondary date relationship) for each measure in the new report? Or is there a different approach I should be looking at maybe for the model itself? (I contemplated duplicating the fact table and maintaining the one primary date-relationship but that too seems redundant. Likely a rookie question but I'm stumped. Or am I overthinking this?
Hi, can you advise on how to handle date columns from 3 different table sources, where date rows are not same, but I want to visualize in a single line chart to see the trend over the years? Currently, I am struggling with making 1 universal date for 3 different tables during the year on monthly basis.
What I am confused about still is the fact that you have an active relationship on order date and in the visual you are displaying the order date. What would happen if you had a shipped date with no corresponding order date? Would the shipped measure for that date show in the visual? Thanks in advance!
Hello Abhishek, You don't have to physically use the "Order Date" and "Ship Date" fields in your reporting visualizations. By creating a virtual relationship using this function between "Order Date (from Sales table) and Date (from Date table)" and "Ship Date (from Sales table) and Date (from Date table)", Date field from date table will handle both of the fields from Sales table accordingly. And by following that approach, it'll still work provided you use date field from date tables in your charts and the measures which are created which evaluates the results accordingly. For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful! If you haven't yet, you can subscribe to our RUclips channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/
What if you want to be able to drill through on either of those dates, I have found it only seems to work on the active relationship where drill throughs are concerned, do you know any ways around this? This has been one of my biggest issues with PBI as many of my report users need the background data but I have many dates on my tables
I think that the best way to solve this problem is having multiple dCalendar Tables in your model, i use this approach whenever its is necessary to use drillthroughs to detail the data...(use the correspond dCalendar)
Thanks @@DanielTavares29 I expected that to be the answer, I was hoping there would be a nicer way to get around the problem as my data has a lot of dates. A previous reporting tool I have used had a drill through function that when applied to a calculation it would activate all filters held within the measure, It would be really handy in PBI
Hi, In this case, you calculated "Total Sales by Ship Date" & doing this was ok. I have to calculate 10s of KPI. Should I write such a 10 Dax formula for each or is there any easy method?
Hello @vikashparihar8366, If you want to analyse the metrics by 10 different dates then you'll have to write 10 different measures, in that case, there're no shortcuts available in that regard. For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our RUclips channel and LinkedIn group to keep posted on the latest data skills and tools updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/
@@EnterpriseDNA Actually, i have 2 dates only. Suppose in your example. If you want to calculate eg shipping cost by shipping date. But use date from other data set (not shipping date data set). Would you create another dax ?
Hi DreamBasel, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming tutorials and announcements. Cheers!
Thanks for the video. I have two identical datasets: one is a historical data and saved as a csv file and the other one is from an API which gets refreshed everyday. I have built all my dashboards based on the API. Now if I follow your steps and append the historical one with the API one, does the data that’s coming from API in the appended table still gets updated or it overwrites the historical one as well? I need to keep the historical data fixed and refresh the API data daily.
Hi Ali, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. The append operation doesn't overwrite anything and it just adds one table below the another which contains same characteristics of information. And while you want historical data in a as-is form and update/refresh the API related data on a daily basis, in that case, you can implement incremental refresh technique in your model. Below are few of the links provided of the documentation from Microsoft's website pertaining to that topic for your reference purposes. For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful! You can subscribe to our RUclips channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla
Hello @onyenetochinedu3371, We request you to please write the entire description of the problem so that the scenario is understandable and assistance can be provided in a better and efficient manner. We're not sure how you're referring about it can be implemented with the "COUNTROWS()" but in our scenario's, we're able to implement it so you can try it in your reporting requirements as well. For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our RUclips channel and LinkedIn group to keep posted on the latest data skills and tools updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/
@@EnterpriseDNA Thanks for your response Below is the issue I encountered and how I was able to work around it 1. I discovered that If you have an existing active relationship within the same table , the ‘Userelationship’ works as a subset of the existing relationship 2. Fix: I had to make sure that the existing active relationship is unchecked (inactive) so that the ‘userelationship’ formula can work independently I don’t know if there is a better way to work around it Thank you
Hi @ajaypatil4083, all pbix file/datasets/resource files are available for download in the Enterprise DNA On-Demand platform, which is accessible via a Subscription. Check out the link below. Cheers! Sign up here: app.enterprisedna.co/sign-up
Note for anyone who might be stuck, you need to have both relationships created, even if the one is inactive. Unsure if I missed this being stated in the video or if it was just inferred. Regardless, thanks for the video, it worked!!
![How To Calculate Running Totals In Power BI Using DAX [2023 Update]](http://i.ytimg.com/vi/fR0dI5UfZD8/mqdefault.jpg)
![How To Calculate Running Totals In Power BI Using DAX [2023 Update]](/img/tr.png)
Check out our FREE courses: bit.ly/3N00AJw
Note for anyone who might be stuck, you need to have both relationships created, even if the one is inactive. Unsure if I missed this being stated in the video or if it was just inferred. Regardless, thanks for the video, it worked!
you saved my life thanks!!
You've taught me so much in PowerBI over the years. I've been watching your videos since 2019.
This issue of multiple dates connecting to a dim date has been an issue for me for ages. I finally took the time to learn how to figure out a way around it. Most of my datasets have 2-3 dates in them to my one DimDate column.
Thanks so much for posting this video.
This just cleared up so many issues. I wish I would've seen vid before I got this far down my power bi journey
Hi @rosswilliams1058, we’re glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!
Thanks for the video. Very well laid out. Have you or anyone else figured out a way to do this with one measure? It would be nice to have a dynamic way to switch between dates with one measure.
Revisiting this video again.. thank you.
Thanks, this is so well explained! I was struggling to make this work, but all good now!
Great video, it will help me a lot.
Very useful indeed! Thanks!
'wicked video> I like the simplicity of the video.... Well explained and easy to understand.
Nice Video - Question if you wanted to have a table list showing two date columns, one is Ordered date, and one is delivered date, how would you solve that issue?
Great stuff, this solved a major issue for me
Thanks for explaining Very useful
Hi @samhall100, we’re glad you found this video helpful! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!
Thank you for this video! The fact that power bi makes you write separate measures for each really feels like a failure of power bi. It obviously CAN have separate connections, it just doesn't want to unless you force it in DAX?
Is there any way i can filter that one? For example, i want the count by ship date but filtering only the ones I receive in Florida for example.
Hey Sam - this one super helpful and yes, it has come up a few times. This is very clear so thanks. I do have a usage question though: I'm rarely using just the one (date-reliant) calculation in the one report (eg: other financial info like GM$, GM% etc over some time period like FY or Month etc). It is worth mentioning that we also have other separate reports of financial information (measures and multiple visuals), based on the PRIMARY date relationship to the same fact table. So would it be best practice to come up with a separate formula/measure (using the secondary date relationship) for each measure in the new report? Or is there a different approach I should be looking at maybe for the model itself? (I contemplated duplicating the fact table and maintaining the one primary date-relationship but that too seems redundant. Likely a rookie question but I'm stumped. Or am I overthinking this?
Hi, can you advise on how to handle date columns from 3 different table sources, where date rows are not same, but I want to visualize in a single line chart to see the trend over the years? Currently, I am struggling with making 1 universal date for 3 different tables during the year on monthly basis.
Excellent !
What I am confused about still is the fact that you have an active relationship on order date and in the visual you are displaying the order date. What would happen if you had a shipped date with no corresponding order date? Would the shipped measure for that date show in the visual?
Thanks in advance!
What about when use both dates in different charts. Will this function work?
Hello Abhishek,
You don't have to physically use the "Order Date" and "Ship Date" fields in your reporting visualizations. By creating a virtual relationship using this function between "Order Date (from Sales table) and Date (from Date table)" and "Ship Date (from Sales table) and Date (from Date table)", Date field from date table will handle both of the fields from Sales table accordingly. And by following that approach, it'll still work provided you use date field from date tables in your charts and the measures which are created which evaluates the results accordingly.
For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! If you haven't yet, you can subscribe to our RUclips channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
What if you want to be able to drill through on either of those dates, I have found it only seems to work on the active relationship where drill throughs are concerned, do you know any ways around this?
This has been one of my biggest issues with PBI as many of my report users need the background data but I have many dates on my tables
I think that the best way to solve this problem is having multiple dCalendar Tables in your model, i use this approach whenever its is necessary to use drillthroughs to detail the data...(use the correspond dCalendar)
Thanks @@DanielTavares29 I expected that to be the answer, I was hoping there would be a nicer way to get around the problem as my data has a lot of dates. A previous reporting tool I have used had a drill through function that when applied to a calculation it would activate all filters held within the measure, It would be really handy in PBI
Hi, In this case, you calculated "Total Sales by Ship Date" & doing this was ok. I have to calculate 10s of KPI. Should I write such a 10 Dax formula for each or is there any easy method?
Hello @vikashparihar8366,
If you want to analyse the metrics by 10 different dates then you'll have to write 10 different measures, in that case, there're no shortcuts available in that regard.
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our RUclips channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
@@EnterpriseDNA Actually, i have 2 dates only. Suppose in your example. If you want to calculate eg shipping cost by shipping date. But use date from other data set (not shipping date data set). Would you create another dax ?
thanks sir!
Hi DreamBasel, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming tutorials and announcements. Cheers!
Thanks for the video. I have two identical datasets: one is a historical data and saved as a csv file and the other one is from an API which gets refreshed everyday. I have built all my dashboards based on the API. Now if I follow your steps and append the historical one with the API one, does the data that’s coming from API in the appended table still gets updated or it overwrites the historical one as well? I need to keep the historical data fixed and refresh the API data daily.
Hi Ali,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
The append operation doesn't overwrite anything and it just adds one table below the another which contains same characteristics of information. And while you want historical data in a as-is form and update/refresh the API related data on a daily basis, in that case, you can implement incremental refresh technique in your model. Below are few of the links provided of the documentation from Microsoft's website pertaining to that topic for your reference purposes.
For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! You can subscribe to our RUclips channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure
learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla
@@EnterpriseDNA thanks very much for the comprehensive response. It was very helpful!
Does the work with COUNTROWS
Hello @onyenetochinedu3371,
We request you to please write the entire description of the problem so that the scenario is understandable and assistance can be provided in a better and efficient manner. We're not sure how you're referring about it can be implemented with the "COUNTROWS()" but in our scenario's, we're able to implement it so you can try it in your reporting requirements as well.
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our RUclips channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
@@EnterpriseDNA Thanks for your response
Below is the issue I encountered and how I was able to work around it
1. I discovered that If you have an existing active relationship within the same table , the ‘Userelationship’ works as a subset of the existing relationship
2. Fix: I had to make sure that the existing active relationship is unchecked (inactive) so that the ‘userelationship’ formula can work independently
I don’t know if there is a better way to work around it
Thank you
Great 👍
Sir, please give data set link.
Hi @ajaypatil4083, all pbix file/datasets/resource files are available for download in the Enterprise DNA On-Demand platform, which is accessible via a Subscription. Check out the link below. Cheers!
Sign up here: app.enterprisedna.co/sign-up
Great
I like your tutorial video, the sound of the keyboard is just kinda annoying, esp. when using earphones. sorry :(
Note for anyone who might be stuck, you need to have both relationships created, even if the one is inactive. Unsure if I missed this being stated in the video or if it was just inferred. Regardless, thanks for the video, it worked!!