Finding list of Measure Tables without going to Query Editor

[rajiv_iyer]

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  

[mahoneypat · Accepted solution]

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

[Greg_Deckler · Accepted solution]

@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.

[rajiv_iyer]

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

[mahoneypat · Accepted solution]

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

[rajiv_iyer]

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