cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frivik Regular Visitor
Regular Visitor

Creating visual with data on the same row

Hi, I've allready recived alot of great help from this community in processing my data. Now it is time to start visualizing it so that it get somewhat more userfriendly. 

 

I've already done quite a lot, but have run into a problem when it comes to visualize when i have data that i wish to display over time on the same row. 

 

The reason for me to have the data structued like this is that i am evaluating long term effect of a treatment, and i use the treatment input to calculate effect over several weeks. 

 

So back to my question: 
Is there a way of getting Power Bi to understand that several columns in the same row is related for example as effect week 1,2,3,4 etc.? So that it can connect dot's between the data in a chart, and not present it as individual dots. 

example data.JPG 
In the picture you can see several  calculated columns showing the results for different life stages of a parasite over time. Would it be possible to get Power Bi to these numbers as a line chart over time even though it is all represented on one row? 

All help appreciated! 

6 REPLIES 6
Super User
Super User

Re: Creating visual with data on the same row

So typically you would unpivot those columns in Power Query. But, since these are DAX calculated columns (I assume), then you might find my DAX Unpivot Quick Measure useful in creating a new calculated table that you could use in your visual.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Frivik Regular Visitor
Regular Visitor

Re: Creating visual with data on the same row

Hi, and thak you for pointing me in the right direction. However, I am relatively new to DAX and Power Bi and were not very succesful, would you mind taking the time to show how the code should be in my sheet? 

The name of the original sheet were the data can be found is [Slice 2015-2019], and the table names is shown in the picture. 

 

In advance thank you! 

 

Cheers

Community Support Team
Community Support Team

Re: Creating visual with data on the same row

hi, @Frivik

 

hi, @Frivik

First you should learn about UNION Function and SELECTCOLUMNS Function in dax.

and Unpivot function in data.

https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221...

Second try these two formulas

 

Table 2 = 

    UNION(
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week1],"Bevegelige uke",[Bevegelige uke 1],"Fastsittende uke",[Fastsittende uke 1]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week2],"Bevegelige uke",[Bevegelige uke 2],"Fastsittende uke",[Fastsittende uke 2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week3],"Bevegelige uke",[Bevegelige uke 3],"Fastsittende uke",[Fastsittende uke 3])
 
    )
Table 3 = 

    UNION(
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week1]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week3]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Bevegelige uke 1]),
       SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Bevegelige uke 2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Bevegelige uke 3]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Fastsittende uke 1]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Fastsittende uke 2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Fastsittende uke 3])

    )

 

Best Regards,

Lin

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

Re: Creating visual with data on the same row

Hi Lin and thank you for taking the time to reply. 

 

I tried your code, but got the same issue as I did myself " The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" 

See pic 1 and 2 - as you can see i only did one small change exchanging Globalweek with a uniqe site treatment ID. But both scenarios produced the same result. 

Union 1.JPGUnion 2.JPG 

Sadly i don't think i can share the full .pbix as it now contains some sensitive data. However, I have included some screenshots of all the categories that i need to un pivot in order to visualize propperly. 


1.JPG2.JPG3.JPG

All of this information is captured on a single row, and is calculated with calculated columns outside of the power query editor, so it is not an option to use.
Basicly the spreadsheet covers what happens when a farmer uses a medical product and follows different aspects in the following weeks - All public data. 

As a uniqe identifier i think the [Yearly site treatment ID] is fitting. 
The goal here is to get somehting like this
Company - Treatment ID - Lice stage (adult female, bevegelig, fastsittende) - week 1 - week 2 - week 3 - week 4
So that i eventually can make a line graph showing the development over time. Upon writing this i actually got an idea and tried, however i'm still getting the same issue. 

4.JPG 

Any suggestions on how to get it right? 

In advance thank you very much for any help! 

Frivik Regular Visitor
Regular Visitor

Re: Creating visual with data on the same row

Hi Lin and thank you for taking the time to reply. 

 

I tried your code, but got the same issue as I did myself " The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" 

See pic 1 and 2 - as you can see i only did one small change exchanging Globalweek with a uniqe site treatment ID. But both scenarios produced the same result. 

Union 1.JPGUnion 2.JPG 

Sadly i don't think i can share the full .pbix as it now contains some sensitive data. However, I have included some screenshots of all the categories that i need to un pivot in order to visualize propperly. 


1.JPG2.JPG3.JPG

All of this information is captured on a single row, and is calculated with calculated columns outside of the power query editor, so it is not an option to use.
Basicly the spreadsheet covers what happens when a farmer uses a medical product and follows different aspects in the following weeks - All public data. 

As a uniqe identifier i think the [Yearly site treatment ID] is fitting. 
The goal here is to get somehting like this
Company - Treatment ID - Lice stage (adult female, bevegelig, fastsittende) - week 1 - week 2 - week 3 - week 4
So that i eventually can make a line graph showing the development over time. Upon writing this i actually got an idea and tried, however i'm still getting the same issue. 

4.JPG 

Any suggestions on how to get it right? 

In advance thank you very much for any help! 

Community Support Team
Community Support Team

Re: Creating visual with data on the same row

hi, @Frivik

Could you please use virtual data to make a simple sample pbix file and expected output. That will be a better understanding of your requirement.

 

Best Regards,

Lin

 

 

 

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