Forum Discussion
Expressions that yield variant data-type cannot be used to define calculated columns.
Hi cengizhanarslan ,
I'm creating this calculated column under Fact table only.
Below one belongs to static it's working. But I need this static way to create. Because I have more than 1,000 company IDs and cannot hardcode company names.
Below is the sample calculated column I used for testing and working:
Exclude Company Rows =VAR YR = YEAR (RELATED ( 'Date'[Date] ))VAR Company = RELATED ( Company[Company ID and Name] )RETURNIF (YR < 2025 ,Company IN {"01 - IBM.","03 - CONZ"},Company <> "28 - Wippro")This approach is not scalable for the real requirement. We need a dynamic solution that responds to the Year slicer selection, instead of relying on fixed company values inside a calculated column.
Please guide me I need to make this dynamic way.
Hi All,
I have created below calculated column, it's working fine.
5Exclude Company Flag =VAR YR = YEAR ( RELATED ( 'Date'[Date] ) )VAR CompanyID = RELATED ( Company[Company ID and Name] )RETURNIF (YR >= 2025 && CompanyID = "28 - Wippro",0,1)
Calculated columns cannot respond to a slicer. They’re computed at refresh time, not at query time. So a “Year slicer” can never dynamically change a calculated column. Instead create a measure that returns 1/0 based on selected year and use it as visual filter on each visual you have.
Include Company =
VAR SelYear = SELECTEDVALUE ( 'Date'[Year] )
VAR IsBlockedCompany = SELECTEDVALUE ( Company[CompanyID] ) = 28
RETURN
IF (
SelYear >= 2025 && IsBlockedCompany,
0,
1
)
Of course the solution below might not workd depending on your measure/visuals, the best solution here would be making these manipulations on your fact table.
Hi cengizhanarslan ,
I'm creating this calculated column under Fact table only.
Below one belongs to static it's working. But I need this static way to create. Because I have more than 1,000 company IDs and cannot hardcode company names.
Below is the sample calculated column I used for testing and working:
This approach is not scalable for the real requirement. We need a dynamic solution that responds to the Year slicer selection, instead of relying on fixed company values inside a calculated column.
Please guide me I need to make this dynamic way.