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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Fabio_B
Frequent Visitor

Line chart linked to field based table

Hi all, 

I'va been able thanks to some of you to create a table based on a Field, that allows me to filter through the coloumn of my file.

It's a simple file with the data of Gas and power across different country:

 

IMG_9748.jpeg

This is the result achieved, as you can see the table on the right is displaying the data selected in the filter on the right:

IMG_9749.jpeg

Now I would like to have a line chart showing the data selected in a graphical way, adding and removing lines based on the selected values through the filter that are populating the table...

I've tried putting the date in the x axis and the field that I've created on the Y axis but is not showing nothing...

If I'm adding on the y axis the data from the excel file instead of the field that I've created the data are shown but are not responsive with the filters.

Can someone please help me?

 

Hope to have been clear, please let me know if any other detail is needed and many thanks to anyone that will help me.

 

Best Reagards

 

Fabio

2 ACCEPTED SOLUTIONS

@Fabio_B This seems to work for me. See PBIX attached below signature. Just unpivot the columns and use the Attribute column as your slicer.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@Fabio_B Just put the Attribute column in the legend.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Fabio_B
Frequent Visitor

Hi @Greg_Deckler, thanks for your feedback, let me try to be more clear.
Actually i have a table like this:

Excel_Energy.PNG

I've created a filter that allows me to filter by column using a "Fileds" (New Parameter - Fileds), achieving this result:

Energy _ Draft.PNG

Now as you can see the table on the right, is showing only the selected data (in this case year 2019 and Italy power and gas values).

I would like to have a line chart showing the same data in a graphical form, something like this:

Chart.PNGThe above chart has been created to have an indication of the desidered output, placing on Y axis years and months. On the x axix i've placed the data.
This chart is not responsive, so if i'm deselecting one of the two values, it won't change.
The same if i'm selecting some more data.

 

The desiderata is to have a chart that shows only the data reported on the table close to the filter on picture 2, adjusting them according to the filter selection as per what the table is actually already doing.

Can you please help me?

 

Many thanks


Fabio

@Fabio_B The big issue is that unless you post your sample data as text it can't be copied and a solution created. Now, all that said, if you unpivoted your data columns I feel like this problem would be solved immediately. Right-click your Month-Year column in Power Query Editor and select Unpivot other columns. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Sorry @Greg_Deckler i haven't understood that you need the raw data.
Please find attached the file that i'm using as database for energy values:

 

Month - YearITALY Power €/MWhITALY GAS €/MWhFrance Power €/MWhFrance GAS €/MWh
gen-1967,6523,6761,1621,92
feb-1957,6720,4646,6218,32
mar-1952,8818,2833,8615,80
apr-1953,3518,6038,0815,11
mag-1950,6717,1537,2113,52
giu-1948,5814,2129,2610,21
lug-1952,3112,5137,6610,87
ago-1949,5411,3733,3910,23
set-1951,1812,5635,549,64
ott-1952,8212,8138,6010,09
nov-1948,1616,1845,9414,20
dic-1943,3414,6636,4613,12
gen-2047,4713,2438,0111,22
feb-2039,3010,7726,259,19
mar-2031,999,9823,838,48
apr-2024,818,6213,456,47
mag-2021,796,5114,864,51
giu-2028,015,8925,794,95
lug-2038,016,3633,414,81
ago-2040,328,1736,757,24
set-2048,8011,4047,2010,64
ott-2043,5713,1937,8913,65
nov-2048,7513,8440,1113,56
dic-2054,0416,2848,4216,00
gen-2160,7119,8659,4820,55
feb-2156,5718,0949,0117,43
mar-2160,3918,2250,2217,34
apr-2169,0221,3663,1019,97
mag-2169,9125,2755,2824,64
giu-2184,8028,0773,5128,34
lug-21102,6635,3278,3735,75
ago-21112,4042,2777,3043,70
set-21158,5962,23135,3161,49
ott-21217,6385,39172,4583,87
nov-21225,9580,49217,0679,86
dic-21281,24113,42274,67113,52
gen-22224,5086,02211,4282,18
feb-22211,6981,45185,5578,78
mar-22308,07126,62295,20123,57
apr-22245,97100,17233,1088,61
mag-22230,0689,82197,4375,85
giu-22271,31102,67248,4093,27
lug-22441,65172,88400,87123,63
ago-22543,15233,53492,49157,27
set-22429,92183,41394,70110,65
ott-22211,5078,05178,8848,43
nov-22224,5191,20191,8869,57
dic-22294,91116,60270,89107,81
gen-23174,4968,37132,1058,15
feb-23161,0756,87148,7650,70
mar-23136,3846,58111,9644,29
apr-23134,9744,83106,3641,68
mag-23105,7334,0777,5529,90
giu-23105,3433,1491,2931,08
lug-23112,0931,4277,6529,11
ago-23111,8933,2190,8732,87
set-23115,7037,0588,7135,79
ott-23134,2643,7384,2641,66
nov-23121,7442,5388,9638,01
dic-23114,5236,3168,5833,01
gen-2499,1631,1976,5928,51
feb-2487,6327,8458,3724,97
mar-2488,8628,7553,5926,42


I've tried unpivoting all the columns but seems not working as well unfortunately.

Many thanks for your help.

Best Regards

 

Fabio

@Fabio_B This seems to work for me. See PBIX attached below signature. Just unpivot the columns and use the Attribute column as your slicer.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks a lot @Greg_Deckler, we're almost there!
Now the chart is showing only one Country/Utility selected, but if i'm selecting more than one it sum the values insted of adding a new line.
Do you think it would be possible to have something like this, adding more than one instead of the sum of the values?

Chart.PNG

 

Again, thanks for the support.


Fabio

@Fabio_B Just put the Attribute column in the legend.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@Fabio_B Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.