Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |