Need to sort by % of parcels that have changed over last 5 years at least once....

[crabcatcherAK]

This is lengthy and I apologize in advance for this specific question to my data set but I have until friday to figure this out.... 

Here is my real estate data, by years 2017, 2018, 2019, 2020, 2021. All data sets appear the same just the values can fluctuate each year as the highlights show. The highlighted data is what my main analysis involves. 

Relationship between the 5 tables in many:Many, only way to get them to talk to eachother. Not sure if this is correct but appears to be working for now. Wouldn't the relationship be many:many since the bulk of the data is the same, some values change year to year. The common link is PID though! 

The 'date' table is from another video, in order to show change over time, they said to create a date table from the beginning of my data to the end. So the table is from 1/1/2017 to 12/31/2021 (not sure if the end date is correct since its in the future) but for now that is what it is. 

Continuing from another video and a previous post of mine here, I created these (3-measures) for each year. I was told these measures are need to show the change/variance between the years as filters are applied on the visuals. 

All of the previous steps lead me to this visual. ( I know it doesn't look pretty, I am still new, but need the data displayed). 

1. Why the weird numbers in 2018? Seems off and not sure why?

2. How to show the % of CDU change? 

3. How to get a count of CDU changes, then filtered by appraiser, nhbd, and/or date? 

Here is another screen shot of some data fields that could be used as filters. 

**Thanks to anyone that can help me tackle this beast. I have a hard deadline and been working to achieve it. I feel as if it's close but need the community expertise to get over this hump. I can provide more clarity and screenshots as needed. 
 

[parry2k]

@crabcatcherAK in PQ add a date column, see attached as an example

✨ Follow us on LinkedIn

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[crabcatcherAK]

I'll run some updates and see if it will open. 

[parry2k]

@crabcatcherAK you can add a new column as a date from the year column, maybe end of the year Dec 31st or first date of the year, doesn't matter and then you can have a relationship with a date column and everything will be just like breeze from there.

[crabcatcherAK]

@parry2k To be clear since I don't know much about power bi, youre saying in my dataset to add a new column for dates? My data set date range goes from 1/1/2017 to basically todays date. How would this formula look (assuming formula)? Or did I miss your point? 

[crabcatcherAK]

@Anonymous @parry2k  I have combined all my data into one table. Everything from 2017 to 2021 as far as house data is concerned. I think my next problem is the date table. I don't have anything date related in my data set to relate a date table to. How to solve this issue? I have all of our inspection histories over the same 5 year period, but what would the common link be? 

[parry2k]

@crabcatcherAK also if multiple people are replying to a post, just make sure to tag the person you are replying/referring to, as a matter of fact, if possible, always tag a person, it helps a lot.

[crabcatcherAK]

@parry2k, @Anonymous, thanks for the quick responses. I just scraped the model and will build a new one. Going to stack Year on top of year to start. Then create a date table to link them together. 

[parry2k]

@crabcatcherAK @Anonymous did a great job explaining but pretty much the same thing that I asked to do. I'm totally in agreement with him that you want to shoot for star schema and go from there otherwise you are looking at the short-term solution not a scalable, long term, flexible, well-performing solution.

[Anonymous]

Seeing the model I can tell you one thing only: GET RID OF THIS "MODEL" AS SOON AS YOU CAN, or even sooner. It's not a model at all. It's a sure way to fail miserably. On top of all the bad things I can see, this model has one fatal flaw: IT'S (very likely) AMBIGUOUS. Therefore don't even try to work with it any further. Unless you want to be in big trouble in no time. Also, many-to-many should never be used with two-way bi-dir filters. NEVER. And many-to-many has been introduced into PBI to solve some very specific problem - that of granularity. You don't have this problem, so the relationships are totally out of place. There would be much more to say about the inadequacy of the "model" but I'm gonna stop here.

I'd kindly suggest that you first learn the principles of proper dimensional design and then study relationships in DAX - what they are for and what problems they bring into the mix. Unless you know all of this, you'll be creating monsters like the one above.

You can start here: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Then you can read through this (on the left hand side of the article the link points to):

[crabcatcherAK]

I read through most of the Microsoft doc stuff after downloading Power BI 10 days ago. I have a general understanding of some of it. But to move forward, this model is trash and I knew it. I was just hoping for a temp solution to give to my superiors. 

That being said, would you agree with the previous comment and append these fact table together by year? Then have a seperate date table to filter with? 

The combo of those two things should get me to see the % change in CDU by year, nhbd, person, or whatever I wanted?? 

[Anonymous]

@crabcatcherAK 

Yes, I would agree that you need one fact table (if you need to append them, then do it) and dimensions around it. This is the right and ONLY right way to do things in PBI. There's a saying in the programming world: "There's never time to do things right but there's always time to do things twice." Do not fall into this trap like so many do. When you start off to do anything, do it RIGHT the very first time. You'll see It'll save you time, energy and money. I know that because I live by this motto all my life and that's also what I'm being paid for (quite well actually :).

[parry2k · Accepted solution]

@crabcatcherAK looking at a very high level this is what I will do:

I will append all these tables together, kind of become a big Fact table and have a date table to have a relationship with a fact table and from there you need only 2 or 3 measures to see the difference between each year. Currently having all these tables and connected to each other doesn't make sense to me.

✨ Follow us on LinkedIn

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡