cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sripriya Frequent Visitor
Frequent Visitor

Re: Level of Detail Calcs

This is not a suggested approach, but you can achieve the FIXED LOD concept in tableau using Group By function in Power BI query editor. Create a duplicate query with the fixed data that you want and later you can merge the data and use it for other visulaizations. This worked for me. Hope this works for you too. Smiley Happy

 

 

 

alm5084 Regular Visitor
Regular Visitor

Re: Level of Detail Calcs

@Sripriya Your solution would technically work, but only with smaller amounts of data. If your grouped query result is several million rows, and your ungrouped query result is 10s of millions of rows, the query might hang forever trying to run. I've had this happen with complicated data heavy queries. The more appropriate solution for calculating a measure at a different level of granularity is to write a level of detail measure in DAX. It can be confusing at first, but once you understand the pattern you can reproduce it easily.

 

If you need a grouped table result - the only reason you would is if you need to use the columns as a categorical axis in a visual - you can create the table using a DAX statement as well. This is the data modeling version of a database view, whereas duplicating the query with a grouped statement would be the database equivalent of actually duplicating the data in a second table. You don't ever want to actually duplicate the data. It not only increases the size and complexity of your Power BI file, it also introduces an additional place for user error when you are updating the data later.  

LPetty Frequent Visitor
Frequent Visitor

Re: Tableau Fixed Level of Detail Calcs

Need to convert the following Tableau Level of Detail Calc to DAX:

{ FIXED [User], [Date]: AVG([Cost])/COUNT([Actions])}.

 

And need to see results by Customer.  I've tried AllExcept without success.

 
Ave cost per date per action =
DIVIDE(
CALCULATE(
AVERAGEX(Sheet1,Sheet1[cost]),ALLEXCEPT(Sheet1,Sheet1[Date],Sheet1[user])),
CALCULATE(
COUNTAX(Sheet1,Sheet1[action]),ALLEXCEPT(Sheet1,Sheet1[Date],Sheet1[user])))
 
Power BI matches the Tableau LoD when viewed in a table with "user" and "date", but when I put "customer" in the table, instead of "user" and "date", the Power BI table results no longer match the Tableau LoD resutls.
 
Link to Power BI example
 
Link to Tableau example
 
I think I just need to figure out how to sum or total up the results at the Customer level.  But I don't know how.