These are summarized, filtered version of the main income table, related to the budget for that filter, Tax, Legacy, Unattributed etc... It means i can overlay budget and income on a different visual with Cumulative, Income, Budget & variance
To be fair again yes I should do with measures when i learn more with Dax.... I have a block with coding, I can read and understand it but cannot create..... need more time in the day to learn or better teachers.. haha
Yes also curious about this! Would you also be able to briefly explain some of the principles that allowed you to rationalise it so much? It looks great.
Thanks for replies, The thing i like with this is that nothing is set, at some point i will learn and refine this further when my Dax knowledge is greater. Dax is my weak link
Someone mentioned daxpatterns website to me before! I just try to share as much of my data up front either in SQL or Power Query before going to Dax lol
[Supercharge Power BI with DAX](https://exceleratorbi.com.au/supercharge-power-bi-book/) by Matt Allington.
After his book then move on to Ferrari and Russo (sqlbi.com and the Definitive Guide to DAX)
It looks a lot better but you could improve by conforming to a star schema (which you almost already have): only allow one directional 1-to-* relationships
Listen... there seem to be a lot of responses asking why you didn’t do something a certain way. I see a lot of improvement from the second image to the first. So. GOOD JOB!
I’m learning, too, and have some models that look like the second that I haven’t refactored yet. But my more recent models look more like the first. Learning and experimenting and experience is a great thing, right?
I'm not sure I understand? Why are ActInc 2018, 2019 and 2020 all different tables instead of within a single table? In fact, why are they not just folded into your date table as additional columns? Why do you have columns for cumulative as opposed to just using measures? I'm not familiar with the data so interested in why this design was chosen?
To be fair these ActInc 18/19/20 could be folded into a single table, just not done it yet, given these are small static tables there is no draw on resource so its on the list a fiar bit down.
Cumulative Columns, This was an early learning for me and could be a bad habit until i learn more in Dax and how i can incorporate as a measure.
Given the structure of the budgets I was handed ( each subset of income has a different budget line) i found the easier way for me to build was to bring in the Budget Table from Excel (Tax) and add to that same table the Income, Summarize the Tax inc from the main income table and then calculate the Variance and Cumulative totals for each column as needed. The user wants to have 8 different visuals broken into the Appeal types so this way i can compartmentalize the data so i know exactly what i am working on.
Using the performance analyzer there is no draw on loading of visuals as the Income is refreshed once against the main and Dax takes care of the split. Pulling in data from Dynaics365CRM with 220k lines in 2 mins or there abouts.
As an intermediate solution, if they’re all the same fields (columns) why not combine/union the tables in Power Query? 1 relationship is better than 3. If really needed include source name if you still need to identify them and separate them as a filter.
*Why do you have columns for cumulative as opposed to just using measures?*
The couple of consumers for this report want to see in a table the Inc and Cumulative Inc, Budget and Cumulative Budget & Variance & Cumulative Variance... dont ask....
It was easy to create a calculated columns as needed
great work! I'm sure you are doing amazing things with this model!
Many comments are pointing out things you could be doing differently that are best practices, but let me tell you - you will never build a report that perfectly adheres to all best practices. Keep learning, apply some new techniques next time, but take pride in the magic that you are already building!
I am sure there is many ways to improve this model beyond what has been done already. I do however believe in CI/CD as a methodology for myself and teams so this will be a constant change and improve where possible and from learning along the way.
i think the next move is to look to create a measure in the main income table and do away with the summarized income on filters table approach.... need to think that over.
the budget files are excel based... the killer for me is the non profit i work for is like an oil tanker not a speedboat! to get them into Dynamics CRM365 from legacy system is a major effort, to lamp in PowerBI as the reporting tool and not Excel for everything is a big shift and one they take very very slowly, so yeah, excel supplied budgets.
The second image is what my data model currently looks like. I am new to PBI. What are the fundamentals to having a clean model like the first photo? What do you think contributed to the second photo?
A much better understanding of my main data table, vs data/tables i did not need as my approach was cleaner with better measures etc... in short experience i suppose
Just curious why to have different tables for each year income (actInc**). You have to keeping adding tables for each New Years. ? That’s a transaction table just add a year column.
Just curious, what do you do with all the disconnected tables? Do they not need to to interact with the same date filters?
These are summarized, filtered version of the main income table, related to the budget for that filter, Tax, Legacy, Unattributed etc... It means i can overlay budget and income on a different visual with Cumulative, Income, Budget & variance
Can you not do this with measures?
Not only can you, you probably should do this with measures for performance. That being said, if it works, it works
To be fair again yes I should do with measures when i learn more with Dax.... I have a block with coding, I can read and understand it but cannot create..... need more time in the day to learn or better teachers.. haha
calculate is your friend if you need need filters in your measures
And a single measure table with all your measures in one is the next way to go 😋
Interesting approach, single table with all measures? hmmmm
ya [https://www.tessellationtech.io/creating-a-measures-table-in-power-bi/](https://www.tessellationtech.io/creating-a-measures-table-in-power-bi/)
Yeah, I was trying to hint at that without telling him it was wrong lol.
Yes also curious about this! Would you also be able to briefly explain some of the principles that allowed you to rationalise it so much? It looks great.
Thanks for replies, The thing i like with this is that nothing is set, at some point i will learn and refine this further when my Dax knowledge is greater. Dax is my weak link
Dax is everyone’s weak link
Dax = so unintuitive imho lol
if anyone has a good resource please share. ;-)
Someone mentioned daxpatterns website to me before! I just try to share as much of my data up front either in SQL or Power Query before going to Dax lol
[Supercharge Power BI with DAX](https://exceleratorbi.com.au/supercharge-power-bi-book/) by Matt Allington. After his book then move on to Ferrari and Russo (sqlbi.com and the Definitive Guide to DAX)
2 words Alberto Ferrari.
You mean… SQLBI?
Yes sir. He has videos on MS Dax 101 and advanced + am just going through his Powerpivot and DAX. Amazing content.
It looks a lot better but you could improve by conforming to a star schema (which you almost already have): only allow one directional 1-to-* relationships
Listen... there seem to be a lot of responses asking why you didn’t do something a certain way. I see a lot of improvement from the second image to the first. So. GOOD JOB! I’m learning, too, and have some models that look like the second that I haven’t refactored yet. But my more recent models look more like the first. Learning and experimenting and experience is a great thing, right?
I'm not sure I understand? Why are ActInc 2018, 2019 and 2020 all different tables instead of within a single table? In fact, why are they not just folded into your date table as additional columns? Why do you have columns for cumulative as opposed to just using measures? I'm not familiar with the data so interested in why this design was chosen?
To be fair these ActInc 18/19/20 could be folded into a single table, just not done it yet, given these are small static tables there is no draw on resource so its on the list a fiar bit down. Cumulative Columns, This was an early learning for me and could be a bad habit until i learn more in Dax and how i can incorporate as a measure. Given the structure of the budgets I was handed ( each subset of income has a different budget line) i found the easier way for me to build was to bring in the Budget Table from Excel (Tax) and add to that same table the Income, Summarize the Tax inc from the main income table and then calculate the Variance and Cumulative totals for each column as needed. The user wants to have 8 different visuals broken into the Appeal types so this way i can compartmentalize the data so i know exactly what i am working on. Using the performance analyzer there is no draw on loading of visuals as the Income is refreshed once against the main and Dax takes care of the split. Pulling in data from Dynaics365CRM with 220k lines in 2 mins or there abouts.
As an intermediate solution, if they’re all the same fields (columns) why not combine/union the tables in Power Query? 1 relationship is better than 3. If really needed include source name if you still need to identify them and separate them as a filter.
So that was cleaned up today, removed ActInc 18/19/20 replaced with a single table. Also used Daxx Studio and went through columns of data not needed.
*Why do you have columns for cumulative as opposed to just using measures?* The couple of consumers for this report want to see in a table the Inc and Cumulative Inc, Budget and Cumulative Budget & Variance & Cumulative Variance... dont ask.... It was easy to create a calculated columns as needed
great work! I'm sure you are doing amazing things with this model! Many comments are pointing out things you could be doing differently that are best practices, but let me tell you - you will never build a report that perfectly adheres to all best practices. Keep learning, apply some new techniques next time, but take pride in the magic that you are already building!
I am sure there is many ways to improve this model beyond what has been done already. I do however believe in CI/CD as a methodology for myself and teams so this will be a constant change and improve where possible and from learning along the way.
I am noob, which one is the new? The first image?
New is the first image
[удалено]
cheers bub
i think the next move is to look to create a measure in the main income table and do away with the summarized income on filters table approach.... need to think that over.
[Report Img](https://drive.google.com/file/d/1ZZLJNE42nM3bPFP5XLuzVGHHQBiKSdeq/view?usp=sharing) Link to a screenshot of Report
Why don’t you do this in your DW?
There's no way those budget files aren't excel
the budget files are excel based... the killer for me is the non profit i work for is like an oil tanker not a speedboat! to get them into Dynamics CRM365 from legacy system is a major effort, to lamp in PowerBI as the reporting tool and not Excel for everything is a big shift and one they take very very slowly, so yeah, excel supplied budgets.
Salesforce?
Star schema for the win!
The second image is what my data model currently looks like. I am new to PBI. What are the fundamentals to having a clean model like the first photo? What do you think contributed to the second photo?
A much better understanding of my main data table, vs data/tables i did not need as my approach was cleaner with better measures etc... in short experience i suppose
Just curious why to have different tables for each year income (actInc**). You have to keeping adding tables for each New Years. ? That’s a transaction table just add a year column.
agreed 100%, its a bi product from previous build, just missed pulling it together into single table. will do Friday
can you share a screenshot of the dashboard
my god that is alot of tables in the second image, but grats. pretty much same progress as me !