Unpivot Issue Eliminates Rows

[mouzicanat]

Hi,

This is my first post and I am new to Power BI, so pelase bear with me.

I have a data set I am pulling from Smartsheet and mainly trying to work out a line graph with two lines (two dates). My X axis are to be dates (months), and my Y axis are $ amount. I want there to be two lines for each type of date, but can't seem to make it work.

After reading other forums, it looks like I need to unpivot my two date columns, BUT, that causes a majority of my rows to disappear from the query. And also gives me an error after I hit 'apply'.

Could anyone help here please?

I'm guessing the error is because I am pulling data and it can't work out where to put it when I unpivot my columns.

Thank you!

[Anonymous]

Without seeing the data its going to be hard to recommend what your issue it.  Whether you need to unpivot the data will entirely depend on whether the data itself is in a pivoted format.

For your example, you simply need a data source where you have the values you want to sum, the date column and then a 3rd column that is the distingishing category to split the data into the 2 boxes.

If your data was in 2 tables, you would instead just have 2 separate columns of data to sum, but would need to like both tables through a date table.

[mouzicanat]

Hi Ross,

Thank you for responding and sorry for not including details!

Here is a link to my dataset, and all columns are in the same table that is actively refreshed.

Let me know if any further details are needed.

Thank you!

[Anonymous]

Sadly my organisations security policy prevents me from opening outside files like that.  Hopefully someone else can assist.

[mouzicanat]

Hi, it's basically the following:

Date1	Date2	Total$
11/20/2018	12/3/2018	15585
11/29/2018	12/17/2018	4650
11/15/2018	11/26/2018	26455
11/20/2018	12/3/2018	7488
12/24/2018	1/18/2019	28235
12/17/2018	1/9/2019	12402
11/26/2018	12/10/2018	1464
12/3/2018	12/17/2018	4320
12/20/2018	1/16/2019	5040
12/17/2018	1/9/2019	5460
11/15/2018	11/26/2018	1917
12/7/2018	12/21/2018	49508.5
12/7/2018	12/31/2018	10380

Aanything helps!

[Anonymous · Accepted solution]

Ok great.  Looks like you won't need to unpivot.  A date table might do the trick and make use of what is discussed in this video:

https://www.youtube.com/watch?v=2BxaUXlx3K4

Basically, make a date table, link to both dates where 1 relationship will be inactive.  Then you create 2 measures and one of the measures makes use of the inactive link with the DAX function called "USE RELATIONSHIP"

[mouzicanat]

Thank you so much Ross! This was really helpful and worked for me, althought I still don't understand fully how 

I should add, that I also had no idea how to create date tables and this site really made it easy for me: https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi hopefully it can help the next person.