The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a DAX Table defined as follows:
JCTransactions =
var JCType=IF(NOT ISEMPTY(vwJCTransactionsRev2),SELECTEDVALUE('ProjectCalc'[Calc Method]))
var JCProject =
SELECTCOLUMNS(vwJCTransactionsRev2,
"JobDivision",[JobDivision],
"JobSortOrder",[JobSortOrder],
"Description",[Description],
"ZeroDetailHours",[ZeroDetailHours],
"JobDetailHours",[JobDetailHours],
"Status",[Status],
"StatusNative",[StatusNative],
"ChangeOrder",[ChangeOrder],
"Cost_CodeSortOrder",[Cost_CodeSortOrder],
"TradeDesc",[TradeDesc],
"CostTypeDesc",[CostTypeDesc],
"SubCostTypeDesc",[SubCostTypeDesc],
"CategoryDesc",[CategoryDesc],
"JobNoYear",[JobNoYear],
"Units",[Units],
"Unit_Cost",[Unit_Cost],
"Amount",[Amount],
"vwLaborHours",[vwLaborHours],
"vwLaborAmt",[vwLaborAmt],
"vwDetailAmt",[vwDetailAmt],
"AccountingYear", IF(JCType="Year",0,[AccountingYear]))
Return JCProject
The Accounting Year column noted above is a variable. If the selected value in a slicer is equal to "Year", then the value should be zero, otherwise the data from the source should be from the [AccountingYear] Column.
So the "AccountingYear" Column in the grid visual the data will either be rolled up for one line item for each project number, or it will have multiple line items displayed based upon the Project Number and the "AccountingYear" column.
This would be either a Summary DAX table or a Detailed DAX Table in the visual grid, based upon the selected value in the slicer.
The results don't change in the AccountingYear column (it's always showing the AccountingYear value, it doesn't shift from "AccountingYear" value to the Zero, yet the selection is displayed correctly in the grid for the JCType Column.
How do I resolve this DAX Code to return the expected results?
Thank you
First, I really need to ask why do you need a calculated table. It seems to me that you only need a disconnected slicer with a dynamic measure for that
Calculated tables aren't dynamic based on slicer selections. They're populated on data refresh and don't change after that.
I have used the slicer in formulas which do recalculate in calucated tables. This one is not working the same way.
Using the SELECTEDVALUE function in DAX - SQLBI
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 August 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
19 | |
19 | |
18 | |
14 |
User | Count |
---|---|
42 | |
35 | |
24 | |
20 | |
19 |