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
rcmv85
Helper I
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_KPIYearCompany_NameValueSymbol

KPI_ATTMCompany_A5.97Company_A 
KPI_ATTMCompany_B0.94Company_B 
KPI_ATTMCompany_C-0.28Company_C 
KPI_A2023Company_AnullCompany_A 
KPI_A2023Company_BnullCompany_B 
KPI_A2023Company_CnullCompany_C 
KPI_A2022Company_A6.15Company_A 
KPI_A2022Company_BnullCompany_B 
KPI_A2022Company_CnullCompany_C 
KPI_A2021Company_A5.67Company_A 
KPI_A2021Company_B2.61Company_B 
KPI_A2021Company_C3.3Company_C 
KPI_B2023Company_AnullCompany_A 
KPI_B2023Company_BnullCompany_B 
KPI_B2023Company_CnullCompany_C 
KPI_B2022Company_A1.1677Company_A 
KPI_B2022Company_B5.2673Company_B 
KPI_B2022Company_C1.4612Company_C 
KPI_B2021Company_A1.2191Company_A 
KPI_B2021Company_B2.5232Company_B 
KPI_B2021Company_C1.4897Company_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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Thank you! It worked!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xinruzhu-msft
Community Support
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"
        )
    )
)

 

vxinruzhumsft_0-1679362230310.png

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.

 

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.