cancel
Showing results for
Did you mean:
Helper I

How to Filter out undesired values before making a calculation?

Hi!

In my powerBI model I've a table with the name "F_KPIs_Worksheet_Table" that looks like this:

 Financial_KPI Year Company_Name Value Symbol KPI_A TTM Company_A 5.97 Company_A KPI_A TTM Company_B 0.94 Company_B KPI_A TTM Company_C -0.28 Company_C KPI_A 2023 Company_A null Company_A KPI_A 2023 Company_B null Company_B KPI_A 2023 Company_C null Company_C KPI_A 2022 Company_A 6.15 Company_A KPI_A 2022 Company_B null Company_B KPI_A 2022 Company_C null Company_C KPI_A 2021 Company_A 5.67 Company_A KPI_A 2021 Company_B 2.61 Company_B KPI_A 2021 Company_C 3.3 Company_C KPI_B 2023 Company_A null Company_A KPI_B 2023 Company_B null Company_B KPI_B 2023 Company_C null Company_C KPI_B 2022 Company_A 1.1677 Company_A KPI_B 2022 Company_B 5.2673 Company_B KPI_B 2022 Company_C 1.4612 Company_C KPI_B 2021 Company_A 1.2191 Company_A KPI_B 2021 Company_B 2.5232 Company_B KPI_B 2021 Company_C 1.4897 Company_C

Now, I've a measure that uses a variable that is defined like this:

``````VAR _MostRecentYear = MAXX(
FILTER(
F_KPIs_Worksheet_Table,
F_KPIs_Worksheet_Table[Value] <> BLANK() && F_KPIs_Worksheet_Table[Year] <> "TTM"
),
VALUE(F_KPIs_Worksheet_Table[Year])
)``````

This variable is to be used for further calculations but I'm getting an error that indicates the following:

``Cannot convert value 'TTM' of type Text to type Number.``

I understand that it can not convert the TTM to the number but that's why I'm filtering out the "TTM" rows of the table before selecting the max year. Or at least this is what I want to do. How can I solve this issue?

1 ACCEPTED SOLUTION
Super User

Hi,

This measure will work

``````VAR _MostRecentYear = MAXX(
FILTER(
Data,
Data[Value] <> BLANK() && Data[Year] <> "TTM"
),
Data[Year])``````

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Super User

Hi,

This measure will work

``````VAR _MostRecentYear = MAXX(
FILTER(
Data,
Data[Value] <> BLANK() && Data[Year] <> "TTM"
),
Data[Year])``````

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thank you! It worked!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @rcmv85

After testing, your measure can work well, or you can try the following measure

``````VAR _MostRecentYear =
INT (
CALCULATE (
MAX ( F_KPIs_Worksheet_Table[Year] ),
FILTER (
F_KPIs_Worksheet_Table,
F_KPIs_Worksheet_Table[Value] <> BLANK ()
&& F_KPIs_Worksheet_Table[Year] <> "TTM"
)
)
)
``````

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors