How to Filter out undesired values before making a calculation?

[rcmv85]

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?

[Ashish_Mathur · Accepted solution]

Hi,

This measure will work

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

Hope this helps.

[rcmv85]

Thank you! It worked!

[Ashish_Mathur]

You are welcome.

[v-xinruzhu-msft]

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.