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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure in Calculated Column

Hi everyone, I', having a DAX problem. I've tried to solve it but to no avail. I'm trying to create a calculated column to flag if a record should show as this year.

 

I have a planning table:

FiscalYear | ReportDate  |  Status   | 
2017       | 01/4/2018   | Open      | 
2018       |             | Open      | 
2017       |             | Closed    |
2016       | 01/10/2016  | Complete  | 
2016       | 01/8/2017   | Complete  | 

I have a measure [Current Year] that returns 2018/19 as text.

I have a date table with every date, sample below of selected years:

FiscalYear | FiscalYearSlash|  Date     | 
2017       | 2017/18        | ..........| 
2018       | 2018/19        | ..........| 
2017       | 2017/18        | ..........| 
2016       | 2016/17        | ..........| 
2016       | 2016/17        | ..........| 

I can return FiscalYearSlash in the planning table with:

CALCULATE(VALUES(Dates[FiscalYearSlash]),
                        FILTER(Dates,
                        Dates[FiscalYear]=Planning[FiscalYear]))

but when I put this as the condition in an IF statement, it comes out false every time. Both the column and the measure are text. Any ideas please?

 

Wondering if it's something to do with context transition. I've watched some of the SQLBI.com videos and understand them but when it comes to applying it.. struggling a bit. Thanks.

Here's the IF statement I'm trying:

IF( CALCULATE(VALUES(Dates[FiscalYearSlash]),
                        FILTER(Dates,
                        Dates[FiscalYear]=Planning[FiscalYear]))
= CurrentYear,"yes","no")

 I need to add a few more conditions also, such as if this other column says x then it's this year and so on but I've fallen at the first hurdle

1 ACCEPTED SOLUTION
Floriankx
Solution Sage
Solution Sage

Hello you could dim your measure inside your formula:

 

VAR CurrentYear=...Concatenate...
RETURN
IF( CALCULATE(VALUES(Dates[FiscalYearSlash]), FILTER(Dates, Dates[FiscalYear]=Planning[FiscalYear])) = CurrentYear,"yes","no")

View solution in original post

3 REPLIES 3
Floriankx
Solution Sage
Solution Sage

Hello you could dim your measure inside your formula:

 

VAR CurrentYear=...Concatenate...
RETURN
IF( CALCULATE(VALUES(Dates[FiscalYearSlash]), FILTER(Dates, Dates[FiscalYear]=Planning[FiscalYear])) = CurrentYear,"yes","no")
Anonymous
Not applicable

Thanks. Funnily enough it occurred to me to try this after posting. I'll have a go now and mark as accepted if it works.

 

General question on the same topic: are measures allowed in calculcated columns?

Hello,

 

generally they are, but calculated columns are row based and measure column based so I try to avoid it if possible.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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