Forum Discussion
Helper ISelf-join to create a measure, columns virtual
Microsoft EmployeeI haven't worked with virtual columns or tables within Power BI, but if you can connect to them through PBI desktop, I don't understand why you can't just work with them the way I described. I am basing my assessment on the example data you gave. If this is the form that your data appears in once loaded into PBI, you should be able to follow the steps I described.
Helper IThanks for you replies @dkay84_PowerBI I wonder if we are talking past each other? If we were talking pivot tables rather than power BI we can pivot the data by dropping the column attribute on the pivot table columns. From there maybe there is a way to define a calculated difference between the two columns using the capabilities of pivot tables in Excel. But I am not aware of anything other than DAX to do the calculations in Power BI. For example, a Matrix could pivot and display the virtual columns as columns, but a Matrix has no calculation capability on its own, so it cannot calculate the difference between the columns.
I am wondering if you know something I do not that would help me make use of your approach. Perhaps you are thinking of a different approach with DAX than the one v-huizhn-msft suggested?
- dkay84_PowerBICan you use the Query Editor to do the pivot and then load that table to the data model where you can use DAX for the calculations?
- uBoatCaptain
dkay84_PowerBI I think you are asking me if I can import the data and convert the virtual columns to actual columns when I do it. I was not aware the Query Editor can do this (which is useful to know), but I know how to do it with SQL anyway. It is just not what I want to do.
My question is really about whether I can model the data in PowerBI the way it comes from the source system. I can leave the columns "vitual" as they are in the source system, or I can pivot them to actual columns as they will almost always be presented. But the latter has some drawbacks I already mentioned and is not my preferred way to go.
I think there should be a way to keep the columns "virtual", as in this case the columns usually correspond to a time period, so they are similar to a date on a sales record. Clearly there are times when users would want to compare sales during one period to sales in a different period. Or expenses in one period to another period. You would not pivot each period to a separate column to do this. Could the DAX time intelligence functions be the answer I am looking for?
v-huizhn-msftHi uBoatCaptain,
If we create a measure, and select it as value field in Martix visual. It will display the corresponding result in each column field. It's unable to only show result in Total column.
Thanks,
Anglie