Forum Discussion
Using column name in calculated column. Dax equivalent to table.columnnames.
Hello,
Is there a DAX function that returns the columnnames as text (or date)?
I need to calculate some values in a column and as a condition I need to use the column names of a table as conditions (I need to use the value which was used at the right date). In Power-Query I could make this work using the Table.ColumnNames function, however I'm working in DAX. I'd rather not change my table with extra columns for the dates, because that would be less clear (this data source will be maintained manually) and I only have to use these dates for this calculation.
Below an example of my table:
| Category | 13-07-2022 | 20-07-2022 |
| Cat01 | 2,3 | 2,4 |
| Cat02 | 2,5 | 2,7 |
I will be using it to calculate with these values corresponding to this other table (the articles (Art01) and categories (Cat01) are linked in yet another table):
| Article | Price | Date |
| Art01 | 2,12 | 14-07-2022 |
| Art01 | 3,54 | 23-07-2022 |
| Art02 | 2,43 | 14-07-2022 |
If you have other ideas I'd love to hear them. I have just started working with Power Bi so I'm sure there are some better ways to do this.
Thanks in advance!
I eventually did have to unpivot the table and referenced the table in order to be able to use the values. It does not feel like the best solution but it does work.
5 Replies
daXtremeSolution Sage
As far as I know, no, you can't do this. In DAX you cannot execute code dynamically as you would, say, using exec() in other languages. Also, Power Query's purpose in life is totally different than DAX's. They are tools for 2 different things.
I eventually did have to unpivot the table and referenced the table in order to be able to use the values. It does not feel like the best solution but it does work.
- daXtreme
Well, unpivoting the first table is what Power BI really wants 🙂 To know how to structure your models to be GOOD (wide and short dimensions, narrow and long facts), please read this article by Microsoft: Understand star schema and the importance for Power BI … (bing.com)
I know, I was just struggling with it because I didn't really want to have these extra dates in my model, because I'm not really sure how to connect these to my date table and I would only need these couple dates as a condition in one if statement. I could do this manually but then it would have to be changed manually each when new columns are added, so I was hoping I could use a function like table.columnnames to get those values automatically. This method could work within Power Query but because the values are divided over two related tables this was unfortunately not possible.
I still don't really know if what I currently have is correct because I now have in both of these two tables a start and enddate, so connecting with the date table is difficult. For the price table I now have an active relationship with the start date and an inactive for the end date, because I don't need that as much.