Power BI newbie; date trouble with line chart visualization

[Usman_Afzal]

Hi all,

I apologise in advance for asking about something probably very basic, but I have tried many things to reach desired outcome. In vain. 

The problem

• Aside from the 3 succesful visualizations I am having trouble with the fourth visualization, a line chart illustrating median sales by date. 
• This visual is based on 2 tables both created with DAX. The two tables don't have any common column and are indirectly related (but not sure about this)
• I do not have any trouble with the other visualizations.

• I have attached the following:
  1. The pbix file, download link: https://easyupload.io/d9fny3
  2. 5 screenshots; the data model, the table relationships, 2 screenshots presenting the visualization problem, and the desired outcome.
• I suspect that I either am doing something wrong in my data modeling, or my DAX code. Can a kind soul please help me, new beginner, understand what I am doing wrong? I hope I have explained properly, but please let me know if I should be more clear 🙂

Thanks in advance!

Power BI Sales report.pbix: https://easyupload.io/qeuwr1

1st created table with DAX

CalendarTable =  

ADDCOLUMNS(

CALENDAR(DATE(2020, 1, 1), DATE(2023, 12, 31)),

"Year", YEAR([Date]),

"Month Number", MONTH([Date]),

"Month", FORMAT([Date], "MMMM"),

"Quarter", QUARTER([Date]),

"Weekday", WEEKDAY([Date]),

"Day", DAY([Date])

)

2nd created table with DAX

Sales in USD =

ADDCOLUMNS(

    Sales,

    "Country Name", RELATED(Countries[Country]),

    "Exchange Rate", RELATED('Exchange Data'[Exchange Rate]),

    "Exchange Currency", RELATED('Exchange Data'[Exchange Currency]),

    "Gross Revenue USD", [Gross Revenue]*RELATED('Exchange Data'[Exchange Rate]),

    "Net Revenue USD", [Net Revenue]*RELATED('Exchange Data'[Exchange Rate]),

    "Total Tax USD", [Total Tax]*RELATED('Exchange Data'[Exchange Rate])

)

[Usman_Afzal]

One thing about the course : the schemas presented are the flat table and star schema, but no mention of snowflake schema.

I don't know if my schema is a star schema or snowflake schema.

I also think it would probably be a good idea for me to look at a few YouTube resources regarding handling multiple fact tables, as I understand from the post of @Wilson_ that I have multiple fact tables in my data model.

[Wilson_]

Usman,

From looking at the screenshot of the model, intuitively I don't think Exchange Rate and Countries should have a relationship. I think it makes more sense for Exchange Rate to have a relationship with the Sales table (or potentially for sales in other currencies to be columns of the sales table directly and not even it's own separate table). This all depends on your use cases for your model, what flexibility you have in building your model's tables, etc.

These changes would turn your model into a star schema design (technically multiple star schemas in the same model since you have multiple fact tables).

Hopefully this gives you more food for thought (and explains why model re-design is not necessarily a simple exercise)!

Happy to answer specific questions if you have any. 🙂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[Usman_Afzal]

Hi again 🙂

Sorry for my delay in response. OK this is what I ended up with, hope it is understandable :

https://easyupload.io/1g3r7v

https://easyupload.io/qcptsu

1. I have used both the measure of Sales Median and the calculated column of Gross Revenue USD.
2. A small settings change of the x axis is done
3. I don't get exactly the same as the exemplar, but in particular with gross revenue usd it's close.

I believe that some different values than mine must have been used in a exemplar, since my graph looks different.

What do you think? 🙂 Your input is very valuable to me @Wilson 

Usman.

[Usman_Afzal]

I can see that the image links are not working, so trying a new host. New image links

 https://ibb.co/kBFMvrS
https://ibb.co/pKkbZ5X

Comments of my previous post apply to these, hopefully working screenshots, what are your thoughts?

[Wilson_]

Hi Usman,

First of all, thank you for providing so much detail, context and clarity about your problem!

You are right, it looks like there is something wrong with the data modeling. Looking at your screenshot of the model, there are many violations of best practices such as:

• Probably unnecessary bi-directional filters
• Relationships between fact tables (purchases and sales)
• No common dimensional table relationships among your fact tables (ie: calendar is only connected to purchases and country is only connected to sales)

I think you would greatly benefit from this free and fantastic data modeling course from SQLBI. Good data modeling will be the foundation of your data journey and well worth the investment upfront!

[Usman_Afzal]

Hi Wilson, 

Thanks for the response and feedback.

Thank you for alerting me about the potential problems. I have been following instructions of an online course.

The course mentioned might definitely be something I will look into!  

[Wilson_]

Awesome, hope it is helpful!

[Usman_Afzal]

Hi again Wilson and everyone else,

No offense intended towards the kind Wilson, but I just want to say that I don't have a solution yet - maybe I ought to seriously delve into the Power BI Data Modeling free course linked to. And aside from being free it's just 100 minutes of videos.

I will look at the course today (the reviews are actually really good!) and let's see if it helps me understand what I am doing wrong with above 🙂

[Wilson_]

Usman,

No offence taken! You're right, there is a bit to digest there. However, I believe updating your data model now will not only help with your problem here but a lot of problems in the future as well.

On a more practical level, I think helping to re-work the data model is something that would require more involvement and collaboration than a simple back and forth exchange on a forum!

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.