How do I copy my query from Dax Query View to Power BI dash board?

[arunnyc]

I have written a Dax Query in Dax Query View. The final result of this query displays a table I would like to display this table on a dashboard. I am new to PowerBI and I am unable to use this. Copying this as a measure gave me syntax errors, though this works in Dax Query View. Can you please let me know how I can use this query and display the resulting table on the dashboard?

(Just a note: This is a self-contained portion of a much larger query that could not be done other than through a Dax query, hence had to resort to writing a query)

EVALUATE
	VAR TimeTable =
	NATURALINNERJOIN(
		NATURALINNERJOIN(
			SELECTCOLUMNS(
				TimeCharges,
				"id", CONVERT(
					TimeCharges[Timesheet Id],
					INTEGER
				),
				"Rate", TimeCharges[Billing Rate],
				"Amount", TimeCharges[Amount]
			),
			SELECTCOLUMNS(
				Timesheet,
				"id", CONVERT(
					Timesheet[Id],
					INTEGER
				),
				"ProjectID", CONVERT(
					Timesheet[Project ID],
					STRING
				),
				"Qty", Timesheet[Hours]
			)
		),
		SELECTCOLUMNS(
			Projects,
			"ProjectID", CONVERT(
				Projects[ID],
				STRING
			),
			"Project Name", Projects[Name]
		)
	)
	 Return TimeTable 
	//(The result had 414 rows)

[arunnyc]

I already did SQL view for this but I was told not to use SQL and they want it all in Power BI. I hope I can convince them otherwise. But given that, with my limited knowledge of PowerBI, I am imagining the required steps to do this in Power query will be as follows:.  I am hoping there is an easier way that I do not know.

1) Merge Timesheet table with the necessary dimension tables. (I can leave out some dimension tables common to both Time and Transaction tables at this stage because I can do that later on the appended table)

2) Delete unnecessary columns not needed for the final report

3) Rename column names to generic names

4)Do all the above steps for the Transactions table

5) Append the two tabes

6) Save the Appended Table with a new name (Does the power query save it using the same name each time, if I do it once manually?)

6) Merge Appended table with the Dimension tables to get any common look-up data (project names etc)

.

My questions:

1) Do you think the Dax method will be still worse compared to the above, despite all these steps?

2) If I create a visual based on the appended query table, will it remain in the data model or will it get broken each time a new appended table is generated?

Thanks for your help.

Arun

[tharunkumarRTK]

@arunnyc 

The result of a DAX measure should be a scalar value. However, the result of a DAX query would be a table. That is reason why you are not able to use this dax query expression as a measure expression.

If not already then I would suggest you to build the model first and add relationships between 'TimeCharges', 'Timesheet' and 'Projects' tables. then in the report canvas add a table visual and include all the columns that you need. If required then you can create measures.

At present you cannot convert your dax query into a visual, However you can there is one option, you can create a calculated table with the below syntax 

TableName = VAR TimeTable =
	NATURALINNERJOIN(
		NATURALINNERJOIN(
			SELECTCOLUMNS(
				TimeCharges,
				"id", CONVERT(
					TimeCharges[Timesheet Id],
					INTEGER
				),
				"Rate", TimeCharges[Billing Rate],
				"Amount", TimeCharges[Amount]
			),
			SELECTCOLUMNS(
				Timesheet,
				"id", CONVERT(
					Timesheet[Id],
					INTEGER
				),
				"ProjectID", CONVERT(
					Timesheet[Project ID],
					STRING
				),
				"Qty", Timesheet[Hours]
			)
		),
		SELECTCOLUMNS(
			Projects,
			"ProjectID", CONVERT(
				Projects[ID],
				STRING
			),
			"Project Name", Projects[Name]
		)
	)
	 Return TimeTable 

then you will be able to add these columns into a table visual.

I would not recommend the calcualted table option, please try to build the data model and then create visuals.

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[arunnyc]

Thanks .  I already have this in my data model. This code was just a portion of the code just to know where to copy and paste this to see the generated table on the dashboard. I am describing my whole issue below so that you can advise me of the best alternative to this Dax table method:

I have two table visuals on my Dashboard coming from the data models I set up. One is this TimeCharges matrix table as in the code I posted, and the other aTrasaction Charges matrix table. These two have dissimilar column names and derive the data from some common and some different tables. I have to present the combined data one below the other (NOT side by side in columns). Given these are not actual Tables but visuals made from the linked tables and also have dissimilar column names, I could not simply APPEND them. I thought the only way would be to create two Dax tables while changing their column names to be generic common names so that I  do a UNION of the two. So my code was as follows:

EVALUATE

    VAR TimeTable = (Code as shown above)

    VAR TransactionsTable = (Similar code as above)

    Var CombinedTable = TimeTable UNION TransactionsTable

     Return CombinedTAble

After reading your reply, I copied the code into a new Calculated Table and it worked, but am now concerned because you said you do not recommend this method. Is it because of the performance /memory issues it creates? What would you suggest I do, given what I am trying to accomplish with this?

Thanks

[tharunkumarRTK]

@arunnyc 
Thank you for explaining your requirement further. Calculated tables are not good practice as they can degrade your model performance. 

Considering your requirement, I would suggest you to do the append operation in Power Query. Before performing the append just make sure you have same structure in both the tables and then perform the append. Conpared to DAX union operation, appending the data in power query would be a much better option.

Or if possible, if your datasource is a SQL database then write a sql query with union command and perform this operation within this SQL. Experts suggest that "Data transformations should be performed as upstream as possible and as down stream as necessary". 🙂

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun