Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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])

tt.PNG

 

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])
)

pbi.PNG
If someone has a workaround for this that'd be great!


Thanks,

Patt

1 ACCEPTED SOLUTION
OwenAuger
Super User
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:

  1. 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)
  2. Remove all other filters
  3. 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

10 REPLIES 10
OwenAuger
Super User
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:

  1. 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)
  2. Remove all other filters
  3. 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hey @OwenAuger

 

Thanks!

This is exactly what I was after.

v-danhe-msft
Employee
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:

1.PNG

Formula:

Measure = CALCULATE(SUM(Table1[A]))
Measure2 = CALCULATE(SUM(Table1[B]))

Result in table:

1.PNG

If I misunderstand you, please just let me know.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 :'(

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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?

Anonymous
Not applicable

I am working with a Transactional Database - with each line in each transaction recorded

 

https://imgur.com/a/mjHA3mc - picture of data set & product hierarchy

 

If you refer to the Tableau Picture I can explain what is happening in each column

 

PA_Description = Type of Promotion

  • This Column shows the different types of Promotions
    • 222, 444, 777.. etc are all Promotion Names
    • Null is everything that was not sold on a promotion

Total Promo Items Sold - formula: {fixed [PA Description] : sum([Qty])}

  • This Column sums the quantity all the products sold ON Promotion for each PA_Description

Total Products Quantity - formula: {fixed [Productcode1] : sum([Qty])}

  • This Column sums ALL the products sold ON Promotion & OFF Promotion for each PA_Description

Promo Redemption - formula: sum([Total Promo Items Sold])/sum([Total Products Quantity])

  • This Column returns the % of Products in a promotion that sold as a promotion.

Example: USING ONE PRODUCT

Product 51 sold 10 units for Promotion 222

Product 51 sold 10 units for Null (not on promotion)

 

PA Description     Total Items Sold In Promo     Total Products Quantity      Promo Redemption

222                     10                                         20                                      50%

Null                     10                                         20                                      50%

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.