Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
hi, @Anonymous
hi, @Anonymous
First you should learn about UNION Function and SELECTCOLUMNS Function in dax.
and Unpivot function in data.
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
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!
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!
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |