Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
sotoc
Advocate I
Advocate I

Line Chart Without Real Dates - Direct Query

Hello,  I have a SQL table populated with scores 0-5 at different places in time. Please note I am using Direct Query in this file so I cannot create any new tables.

 

CustIDcurrent_score6_months_ago12_monthsago18_months_ago24_months_ago
A132223
B10144blank
C123blankblank5

 

I need to create a line chart for the average of scores at each point in time:

x-axis is the points in time (current_score, 6_months_ago, 12_months_ago, etc.)

y-axis is the average score (first dot will be average of current_score = ((3+0+2)/3).

 

So I will have 5 dots across the length of the visual with lines connecting those dots.
I do have blanks (not nulls) in the dataset that need to be trimmed. 

 

Thanks for any help!

Carly

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @sotoc 
Open edit queries,
Click on "CustID" column, then select "Transform->Unpivot columns->Unpivot other columns",

1.png

Close&&apply

Create a column

Column = IF([Value]="blank","0",[Value])

Then change its data type to "whole number"

3.png

 

Then Create a measure

Measure 2 = CALCULATE(AVERAGE('Sheet1$'[Column]),ALLEXCEPT('Sheet1$','Sheet1$'[Attribute]))

2.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @sotoc 
Open edit queries,
Click on "CustID" column, then select "Transform->Unpivot columns->Unpivot other columns",

1.png

Close&&apply

Create a column

Column = IF([Value]="blank","0",[Value])

Then change its data type to "whole number"

3.png

 

Then Create a measure

Measure 2 = CALCULATE(AVERAGE('Sheet1$'[Column]),ALLEXCEPT('Sheet1$','Sheet1$'[Attribute]))

2.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.