Creating visual with data on the same row

[Anonymous]

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. 

 
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! 

[v-lili6-msft]

hi, @Anonymous

hi, @Anonymous

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-f588221c7098

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

[Anonymous]

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. 

 

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. 

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. 

 

Any suggestions on how to get it right? 

In advance thank you very much for any help! 

[Anonymous]

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. 

 

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. 

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. 

 

Any suggestions on how to get it right? 

In advance thank you very much for any help! 

[v-lili6-msft]

hi, @Anonymous

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

[Greg_Deckler]

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

[Anonymous]

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