Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
vsslasd1
Helper III
Helper III

DAX Table with a variable based upon a selected value in a silicer

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

 

3 REPLIES 3
Kucrapok
Helper I
Helper I

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

PaulOlding
Solution Sage
Solution Sage

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


Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors