Forum Discussion
Tableau User - LoD Functions in PowerBI dont work the same? plz halp
Hey guys.
I am trying to do a LoD Calculation in Power BI - but it doesn't work like it does in Tableau and I cant figure out what I am doing wrong.
Calculations I am using in Tableau Listed Below
Total Promo Items Sold - "This Calculations sums all items sold on a specific promotion PA Description"
{fixed [PA Description] : sum([Qty])}
Total Products Quantity - "This Calculation sums all the items sold that could of been in the promotion"
{fixed [Productcode1] : sum([Qty])}
Promo Redemption - "This Calculation shows the Percentage of Products that are sold on Promotion"
sum([Total Promo Items Sold])/sum([Total Products Quantity])
So I am trying to gather all Productcodes in PA Description and sum the quantity of those items
Power BI Formulas I have.
Total Promo Items Sold: - WORKS!
Total Units = CALCULATE(
SUM('data'[Qty]),
ALLEXCEPT('data', 'data'[PA_Description])
)
Total Products Quantity - Does not give me the correct answer :'(
Total Products Quantity = CALCULATE(
SUM('data'[Qty]),
ALLEXCEPT('data', 'data'[Productcode])
)
If someone has a workaround for this that'd be great!
Thanks,
Patt
Anonymous
The Power BI equivalent of a Tableau FIXED LOD expression is a CALCULATE ( ...., ALL(...), VALUES(...) ) pattern.
This is because, in Tableau, the expression {fixed [PA Description] : sum([Qty])} translates to:
- Create a filter equivalent to the currently visible values of [PA Description] (whether due to direct filters on [PA Description] or cross-filtering from other columns)
- Remove all other filters
- Calculate SUM([Qty]) in this new context
To get the same result in Power BI using the data from POWER BI DATA.xlsx, I would rewrite your two measures as:
Total Units = CALCULATE( SUM('data'[Qty]), ALL ( 'data' ), VALUES ('data'[PA_Description] ) )
Total Products Quantity =
CALCULATE(
SUM('data'[Qty]),
ALL ('data' ),
VALUES ( 'data'[Productcode] )
)In situations where the column you want to retain as a filter is in a related table, you may need to use SUMMARIZE rather than VALUES.
Here is a good article related to what you are doing here:
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
Regards,
Owen
10 Replies
- OwenAuger
Super User
Anonymous
The Power BI equivalent of a Tableau FIXED LOD expression is a CALCULATE ( ...., ALL(...), VALUES(...) ) pattern.
This is because, in Tableau, the expression {fixed [PA Description] : sum([Qty])} translates to:
- Create a filter equivalent to the currently visible values of [PA Description] (whether due to direct filters on [PA Description] or cross-filtering from other columns)
- Remove all other filters
- Calculate SUM([Qty]) in this new context
To get the same result in Power BI using the data from POWER BI DATA.xlsx, I would rewrite your two measures as:
Total Units = CALCULATE( SUM('data'[Qty]), ALL ( 'data' ), VALUES ('data'[PA_Description] ) )
Total Products Quantity =
CALCULATE(
SUM('data'[Qty]),
ALL ('data' ),
VALUES ( 'data'[Productcode] )
)In situations where the column you want to retain as a filter is in a related table, you may need to use SUMMARIZE rather than VALUES.
Here is a good article related to what you are doing here:
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
Regards,
Owen
Hey OwenAuger
Sorry to bother you.
I was wondering if you know how to apply slicer filters to the DAX code?
In Tableau they are called 'context filters' - This filters the data before the calculations occurs. This makes them Dynamic which is awesome!!
I have found a way to do that involves me creating ALOT of tables and referencing them but this would take me days to complete.
If you know of anything that could help that would be amazing.
Thanks,
Patrick- OwenAuger
Hi again Anonymous
Could you describe exactly how you want to use the "context filter" in this case and we might be able to come up with a solution.
There isn't a direct equivalent of Tableau's context filters in Power BI. All filters (report, page, visual, slicer or otherwise) are intersected simultaneously when DAX expressions are evaluated.
Off the top of my head, one idea is that we could produce the same effect as a context filter by using a disconnected table to provide the "context filter" and wrap a CALCULATE (..., TREATAS(...) ) around the rest of the DAX expression to create a filter that takes precedence over other filters.
Regards,
Owen
What is the relationship of Product Code with your data? Based on the context of how your running this measure within the visual i'm not surprised you are getting this particular result.
The statement you have written is essentially saying "Ignore all filtering context, except for the filtering context applied to the Product Code". In the table you have shown, there is no product code item. This indicates to me that there is no Product Code context to maintain.
There will be a solution, we just need to understand your data better.
Hi Ross,
Product Code is a unique product identifier
PA_Description defines a promotion
- A Promotion can contain multiple Product Codes
- Different Promotions can contain the same Product Code
I'm not sure how to upload a workbook or dataset on here so I've uploaded them to Hightail so anyone can download
https://spaces.hightail.com/space/kajDUnf52K
- Excel Data Set
- Power BI Workbook
- Tableau Workbook
Sadly my companies polices mean i'm prevented from downloading your files.
What we need to understand is what is the logic here for the count? Based on your formulas, you are reusing the same QTY column for your count. So given a particular PA_Description, what logic are you wanting to apply to get the other count? Is it a case of getting the distinct values of the Product Codes, then finding out what the total quantity of those product codes are added together?
v-danhe-msftMicrosoft Employee
Hi Anonymous,
Based on my test, in Power BI, the data could be aggregated in the table, you could just use the CALCULATE(SUM(YourTable[yourcoulumn])) to get the correct result.
Sample:
Formula:
Measure = CALCULATE(SUM(Table1[A])) Measure2 = CALCULATE(SUM(Table1[B]))
Result in table:
If I misunderstand you, please just let me know.
Regards,
Daniel He
Hey v-danhe-msftthanks for getting back to me!
I could aggregate it but I need some of the other values at a lower level than PA_Description so this won't work for me :'(