The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
This produces the error "A function 'Calculate' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
CALCULATE ( LASTNONBLANK ( RunData[DT_Level], 1 ), RunData[fk_GeneratorName_ID] = SelectedGenerator, RunData[RunData_Date] = CALCULATE ( MAX ( RunData[RunData_Date] ), ALLEXCEPT ( Tanks, Tanks[Tank_Name] ) ) )
I am trying to figure out something with this code:
LastTotalDayTankLevel = VAR SelectedTank = SELECTEDVALUE ( Tanks[Tank_Name] ) VAR SelectedGenerator = LOOKUPVALUE ( Tanks[fk_GeneratorName_ID], Tanks[Tank_Name], SelectedTank ) VAR IBXID = LOOKUPVALUE ( IBXs[IBX_ID], IBXs[IBX_Name], SELECTEDVALUE ( IBXs[IBX_Name] ) ) VAR LastLevelDate = CALCULATE ( MAX ( RunData[RunData_Date] ), ALLEXCEPT ( Tanks, Tanks[Tank_Name] ) ) RETURN IF ( ISINSCOPE ( Tanks[Tank_Name] ), CALCULATE ( LASTNONBLANK ( RunData[DT_Level], 1 ), RunData[fk_GeneratorName_ID] = SelectedGenerator, RunData[RunData_Date] = LastLevelDate ), SUMX ( SELECTCOLUMNS ( FILTER ( ALL ( Tanks ), [fk_IBX_ID] = IBXID ), "tID", [fk_GeneratorName_ID] ), CALCULATE ( LASTNONBLANK ( RunData[DT_Level], 1 ), FILTER ( RunData, AND ( RunData[RunData_Date] = LastLevelDate, AND ( RunData[fk_IBX_ID] = IBXID, RunData[fk_GeneratorName_ID] = [tID] ) ) ) ) ) )
The problem I am having is that by using VAR LastLevelDate I am only getting one date. I need to be able to have the last date for each individual tank name which means many dates.
(i.e. Tank-x has last date of 8/1/2019, Tank-y has a date of 8/8/2019, Tank-z has a date of 8/15/2019. As written the code only gives me 8/15/2019 so I only get a value for Tank-z)
PBIX file is here.
Solved! Go to Solution.
Figured out to make the compared value a Variable and it gets rid of the error.
Hi @tkrupka ,
You could use ALLEXCEPT() function to group by tank's name. Could you share your files? It is difficult to understand your table via the DAX.
If the last date is all the same I get the following:
If the last date is not the same for all tanks, I get this:
There should always be a value for every tank.
Should I use a calculated column?
Am I thinking correctly that a calculated column is updated with a dataset refresh? If so, I may be able to figure out how to use this.
Figured out to make the compared value a Variable and it gets rid of the error.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
94 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
121 | |
81 | |
79 |