cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajiv_iyer
New Member

Finding list of Measure Tables without going to Query Editor

Dear Experts,

I've started experimenting with Power BI and am learning DAX Measures now. I created a Measure Table by the name "Running Totals" and before creating a measure, I deleted the default Column 1 from the model. The "Running Total" table was now not visible. I thought maybe if you delete Column 1 without creating a measure, the entire table gets deleted. I started the process of again creating a Measure Table but was not able to name it as "Running Totals" because I got the error "Table already exists". I tried checking "View Hidden" but still could not fing the original "Running Totals" measure table. The only way I could delete the original table is by clicking on "Transform Data" and deleting the original "Running Totals" table and renaming the "Running Totals 2" table to what it should be. Is this a bug that hides a Measure table if we delete Column 1 without creating a measure? Why is it not visible under "Fields"? Is there a way to do what I did instead of going to the Query Editor?

 

Thanks,

Rajiv  

2 ACCEPTED SOLUTIONS
mahoneypat
Super User
Super User

You should see all tables in the Diagram View, or you could look at your model with Tabular Editor.  FYI that a simple was to make a measures table is with a DAX table (Modeling Tab/New Table), using and expression like this

 

.Measures = {BLANK()}

or

.Measures = {NOW()} // or TODAY(), if you want to capture the date or datetime of last refresh when the whole model refreshes after publishing it

 

I put the . before Measures so it will be alphabetically first in the list of tables.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Greg_Deckler
Super User
Super User

@rajiv_iyer I'll chime in here along with @mahoneypat. There are two methods that I have experimented with to create measure tables, Power Query and DAX. They work differently in certain circumstances like the one you describe. I prefer the Power Query approach and I'll explain why.

 

First, the DAX approach is how @mahoneypat described. In this circumstances, if you do not put a measure in the table and delete the default Column that is created, the table remains in the model and you can add measures to it. What I don't like about this method is the warning label you get that the expression is not a valid table expression. Also note that if you delete the default column and put a measre in the table, regardless of the name of the table, the icon will change and the table will appear at the top of the Fields list.

 

OK, the Power Query approach. Create a Blank query and name it whatever. Right-click and choose to not include it in data refresh but keep Enable load on. Load the table. Put a measure in the table and then delete the default column, which will be the name of the query. Then you have your measure table, at the top of the Fields list regardless of the name with the special "measure table" icon. Now, if you delete the column first without putting a measure in, the entire table disappears from the Fields list and you have to go back into Power Query Editor, delete the query and try again.

 

As I said, I prefer the Power Query approach because you don't get the warning label that way.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@rajiv_iyer I'll chime in here along with @mahoneypat. There are two methods that I have experimented with to create measure tables, Power Query and DAX. They work differently in certain circumstances like the one you describe. I prefer the Power Query approach and I'll explain why.

 

First, the DAX approach is how @mahoneypat described. In this circumstances, if you do not put a measure in the table and delete the default Column that is created, the table remains in the model and you can add measures to it. What I don't like about this method is the warning label you get that the expression is not a valid table expression. Also note that if you delete the default column and put a measre in the table, regardless of the name of the table, the icon will change and the table will appear at the top of the Fields list.

 

OK, the Power Query approach. Create a Blank query and name it whatever. Right-click and choose to not include it in data refresh but keep Enable load on. Load the table. Put a measure in the table and then delete the default column, which will be the name of the query. Then you have your measure table, at the top of the Fields list regardless of the name with the special "measure table" icon. Now, if you delete the column first without putting a measure in, the entire table disappears from the Fields list and you have to go back into Power Query Editor, delete the query and try again.

 

As I said, I prefer the Power Query approach because you don't get the warning label that way.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@Greg_Deckler and @mahoneypat Thanks a lot for your responses. Learnt something new and will keep both approaches in mind. 

mahoneypat
Super User
Super User

You should see all tables in the Diagram View, or you could look at your model with Tabular Editor.  FYI that a simple was to make a measures table is with a DAX table (Modeling Tab/New Table), using and expression like this

 

.Measures = {BLANK()}

or

.Measures = {NOW()} // or TODAY(), if you want to capture the date or datetime of last refresh when the whole model refreshes after publishing it

 

I put the . before Measures so it will be alphabetically first in the list of tables.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hi Pat,

Thanks for your reply and also the tip on how to use Measure more effectively. Beginner level question here - I can't locate the Diagram view nor the Tabular Editor (looked it up on Google - guess need to download it separately).

 

Regards,

Rajiv

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!