Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
When I started working on this I thought it would be simple, however, it is taking a toll on me.
This is my problem:
I have this data-model, which combines three tables.
I am trying to cut the data base on some dates contained in other tables, more specifically, I want to cut FactProjExp with a selected value from DimInstance_Project[CutDate]:
Note that within PowerBI I am forcing the selection of only one instance.
The simple formula I am using is:
FYBudgetExpenses =
var ExpensesCutDate = SELECTEDVALUE(DimInstance_Projects[CutDate])
var Actuals = CALCULATE(
SUM(FactProjExp[Amount]), FactProjExp[FactDate] < ExpensesCutDate)
return Actuals
However, this is bringing in no data.
If I manually set-up the date it works though, I just can get my head around on why this is happening or if there is any unintended relation.
Many thanks!
Solved! Go to Solution.
Hi @powerbr ,
If you want to use SELECTEDVALUE(DimInstance_Projects[CutDate]) as the filter result of the slicer, there must be no relationship between your DimInstance_Projects table and the FactProjExp table.
If you want to add up the values on April 5, 2021, you can add the equal sign.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@powerbr You're getting no results because the DAX you're using conflicts with the relationships. You either need to delete the relationship between the two tables DimInstanceProj and FactProjExp or use ALL or ALLSELECTED.
Not sure if this post will help you much as you're almost there, but similar to what's being done with approximate lookups: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison, thanks for the reply.
I did try this:
FYBudgetExpenses =
var ExpensesCutDate = SELECTEDVALUE(DimInstance_Projects[CutDate])
var Actuals = CALCULATE(
SUM(FactProjExp[Amount]),
FILTER(ALL(FactProjExp[FactDate]), FactProjExp[FactDate] < ExpensesCutDate))
return Actuals
But it still didn't show any result.
I did created a model w/out the relations but I still got the same error. I think I am doing things on a non-PBI way and trying to fit a different reasoning, who knows.
Now I am just adjusting the query in the back, but I guess this means my data will grow exponentially.
I will try the approach from your blog one more time, maybe I missed something, and come back to you. Thanks again!
Hi @powerbr ,
If you want to use SELECTEDVALUE(DimInstance_Projects[CutDate]) as the filter result of the slicer, there must be no relationship between your DimInstance_Projects table and the FactProjExp table.
If you want to add up the values on April 5, 2021, you can add the equal sign.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, this works.
I am marking this down as a solution.
I have a doubt, if I create a relationship but it only filters one way (Ideally, I want to filter those tables as well based on dates), will this have an impact?
I am going to be trying this.
Doing this task made me learn lots about PBI, much appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |