Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Rachel_123
Helper I
Helper I

Key Column can't be defined in calculated tables?

Hi,

I have a calculated table with a unique column that I am trying to assign as a key column in the model view, PBI doesn't give me the option to select a column as key column for any calculated table, only normal tables and the ones created in power query, is this an expected behaviour?  I am trying to identify a unique key to avoid a circular dependency

Rachel_123_0-1680885821567.png

 

 

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

By default , it is not possible.

 

Alternative way exists:

a) There is one more way, that you can launch Tabular Editor "TE" and change.

     TE, an external tool you have to install. https://tabulareditor.com/downloads

      Download the free version. You dont need to buy the latest version for this.

 

b) Once you have Tabular Editor installed, open .pbix file, launch tabular editor.

sevenhills_0-1680890114930.png

 

c) Navigate on the left to the column of the table.

Say, Delete Summary table, Source.Name is the key column you want to configure.

sevenhills_1-1680890354600.png

 

d) Properties window will show up.

Set the  "Key"  as "True"

sevenhills_0-1680891097303.png

 

Save and close the TE. You are good to go!

 

e) In the model view, you will see the key is set.

 

FYI, By doing this, you can see in the diagram pane and rows selection as key column. But not in the properties window. May be intentional feature by Microsoft team or a bug 🙂 

 

sevenhills_2-1680890521334.png

 

sevenhills_3-1680890529648.png

 

sevenhills_4-1680890542658.png

 

Hope this helps!

 

Note: PBI is nothing but SSAS tabular enhanced version.  

 

 

View solution in original post

5 REPLIES 5
sevenhills
Super User
Super User

By default , it is not possible.

 

Alternative way exists:

a) There is one more way, that you can launch Tabular Editor "TE" and change.

     TE, an external tool you have to install. https://tabulareditor.com/downloads

      Download the free version. You dont need to buy the latest version for this.

 

b) Once you have Tabular Editor installed, open .pbix file, launch tabular editor.

sevenhills_0-1680890114930.png

 

c) Navigate on the left to the column of the table.

Say, Delete Summary table, Source.Name is the key column you want to configure.

sevenhills_1-1680890354600.png

 

d) Properties window will show up.

Set the  "Key"  as "True"

sevenhills_0-1680891097303.png

 

Save and close the TE. You are good to go!

 

e) In the model view, you will see the key is set.

 

FYI, By doing this, you can see in the diagram pane and rows selection as key column. But not in the properties window. May be intentional feature by Microsoft team or a bug 🙂 

 

sevenhills_2-1680890521334.png

 

sevenhills_3-1680890529648.png

 

sevenhills_4-1680890542658.png

 

Hope this helps!

 

Note: PBI is nothing but SSAS tabular enhanced version.  

 

 

Thank you so much, my company policy won't allow this but hopefully it will help others! 

Oops, I don't know any other way.

 

FYI, You don't need admin permission to install Tabular Editor. If the policy does not allow, it is tough. 

 

Alex_Sawdo
Helper II
Helper II

As far as I'm aware, you can't define a key column for a calculated table. An alternative solution could be to either create the table in the source of your data or to create the table using Power Query. With either of these solutions, you can add in a key column. 

oh no 😞 I was hoping there's some other way, I only know dax to create tables, not good at M  language 😞 

 I need to create a summary table from another table, is there an easy way to create this table in power query? 

Projects Summary = SUMMARIZE('Portfolio Reporting','Portfolio Reporting'[Project],'Portfolio Reporting'[Expense Type], 'Portfolio Reporting'[Date],"Actuals",CALCULATE(sum('Portfolio Reporting'[Value]),'Portfolio Reporting'[Versions] IN { "Actuals" }),"Current Budget", CALCULATE(SUM('Portfolio Reporting'[Value]),'Portfolio Reporting'[Versions] IN { "Current Budget" }),"Current Forecast",CALCULATE( SUM('Portfolio Reporting'[Value]),'Portfolio Reporting'[Versions] IN { "Current Fcst" }))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.