Forum Discussion
Helper IIALL filter not working on fact table ?
Hi All,
I am trying to use ALL to remove a column from filtering on my Sales fact table and it is not working. It works fine when I using ALL to remove filters from dimension tables. How can I do this to remove the filter context for Sales[Profit Buckets]? I want the Local Sales total for all Profit Buckets so I can use that in another calculation.
For example here I would expect to see the circled 172 bil number on all rows.
Why is ALL working differently on a dimension table vs this Sales fact table?
Thanks,
Dustin
dusdau first it is the biggest design flaw, you should be using Dimension and fact, full stop. You can surely solve it with DAX but why complicate things, anyhow following will work
Measure = CALCULATE ( SUM ( Table[Amount] ), ALL ( Table ), VALUES ( Table[Fiscal Year] ) )✨ Follow us on LinkedIn and to our YouTube channel
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
6 Replies
- mh2587
Super User
Try removefilter()
dusdauTried that previously, same result
- parry2k
dusdau you have to use ALL ( TableName ) not ALL ( Table[ColumnName] )
✨ Follow us on LinkedIn and to our YouTube channel
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
- dusdau
Thanks, but that removes ALL filters, not just the Profit Bucket filter? So if I want to remove Profit Bucket filter but include a Fiscal Year filter I get this:
Here I would want to see the FY year total in each column, regardless of profit bucket....if that makes sense?
The end goal is to calculate the % each Profit Bucket is of the FY Total to see change over Time (Fiscal Year).
- parry2k
dusdau first it is the biggest design flaw, you should be using Dimension and fact, full stop. You can surely solve it with DAX but why complicate things, anyhow following will work
Measure = CALCULATE ( SUM ( Table[Amount] ), ALL ( Table ), VALUES ( Table[Fiscal Year] ) )✨ Follow us on LinkedIn and to our YouTube channel
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
- dusdau
Thanks for your feedback. I don't want to complicate my life any further. 🙂
Since I need to bucket the %'s for each line in the fact table, would you recommend creating a numeric index/key for each bucket in the fact table and then create a relationship to a dimension table like:
BucketIndex1 Negative 2 0-10% 3 10-20% Etc. Or is there some better way?
