The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Guys,
I'm from Brazil, and I have this report, and this shows me Sells by Date and Payment Method:
https://www.dropbox.com/s/mx8dxkusdztvd2m/REPORT.png?dl=0
To the left, some date filters (respective lastDays, year, quarter, month and day), that comes from a calendar table i've just tailored, relationed by date.
The point is that data actually comes from another database - my sells database.
This database drills into Date > Region > Payment Method > Values, and all the information is properly summarized (it was my last challenge, already completed).
My issue is that when I drill into "apr 2014", on "Boleto" paymentMethod specifically, it shows me actual data with some missing points, because not all days we've sold to every region, nor by every paymentMethod available:
https://www.dropbox.com/s/n0wy0ih404ize75/REPORT_1.png?dl=0
I wish I could show all date-points, even where it misses the data, to analyze the behavior of the indicator along the days.
Since I've just tailored a calendar table (as stated before), I thought that it alone would solve this problem, since all the date-points in my desired range are in the table.
My old Excel database had auxiliar observations with dates from 20130101 to 20161231, for instance, and by that time, just to avoid leaving the paymentMethod column blank, I've put manually "Depósito".
I used to do this to avoid missing date-points in Excel.
When I migrated to Power BI, I started drilling data into a more detailed level.
That proxy indirectly solved the problem I described here in parts, because virtually, we have "Depósito" for all the days in the range:
https://www.dropbox.com/s/547d4c4i64vqzvj/REPORT_2.png?dl=0
I've mentally found a sollution to this problem - by tailoring a database with observations for each day between 20130101 to 20193112, and multiplying it by Region, and after by Payment Method. That could leave me with a database of:
2.922 (dates) X
8 (Regions) X
7 (Payment Methods)
Total: 163,632 observations X 3 columns
Since I don't believe that's the best way to do that, could you guys figure how could I do that?
Regards,
Michell Madeira
Solved! Go to Solution.
Would need sample data and your relationships and your formulas to really help you. What I can say is that whatever formula that you are using to calculate your sales or whatever, you should be able to wrap in an IF statement with an ISBLANK or something and return a "0" or something for that day so that it has some value for that day. Another trick is to add a column with the formula:
Column = 1
Just include that column in some way in your visualizations and now everything has a value so they do not get filtered out.
Again, these are general suggestions, I can't really give you specifics with the information that you have included.
Would need sample data and your relationships and your formulas to really help you. What I can say is that whatever formula that you are using to calculate your sales or whatever, you should be able to wrap in an IF statement with an ISBLANK or something and return a "0" or something for that day so that it has some value for that day. Another trick is to add a column with the formula:
Column = 1
Just include that column in some way in your visualizations and now everything has a value so they do not get filtered out.
Again, these are general suggestions, I can't really give you specifics with the information that you have included.
As a self learner, I have never got access to this function before.
It worked flawless:
About putting an extra auxiliar column, this is a technique that I often use, but in this case, maybe my filters would not make it work as we wanted.
Thank you very much, smoupre!
Regards,
Michell Madeira
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
80 | |
80 | |
48 | |
40 |
User | Count |
---|---|
150 | |
110 | |
64 | |
64 | |
58 |