How to use ISFILTERED with condition

[Anonymous]

Hello Power BI Community

I have some trouble to figure out how to use Dax Isfiltered function.

This is my current meassure that calculate YoY expression. When you select categories is shows N/A and this works.

Now I also want to apply isfiltered to MM-YYYY aswell, all before year 2019 should show N/A and Year before works normally with YoY

YTD visual V/C Ratio =

VAR VC_Ratio = DIVIDE([CV_TOTAL_VOLUME_YTD],[YTD Total cases],0)

Return

IF(ISFILTERED(SALESFORCE_CASES_FCT_20210429[Case_origin group]), "N/A",VC_Ratio)

The visual

The meassure:

Thanks you

Khanh Tu

[selimovd · Accepted solution]

Hey @Anonymous ,

if you want to check for a specific value, you should use the SELECTEDVALUE function.

So you can check if [Case_origin group] or if the selected year is 2019 and then return "N/A" or otherweise return the value:

YTD visual V/C Ratio =
VAR VC_Ratio =
    DIVIDE(
        [CV_TOTAL_VOLUME_YTD],
        [YTD Total cases],
        0
    )
RETURN
    IF(
        ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || SELECTEDVALUE( myDate[Year] ) = 2019,
        "N/A",
        VC_Ratio
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[PaulDBrown]

Try:

YTD visual V/C Ratio =

VAR VC_Ratio = DIVIDE([CV_TOTAL_VOLUME_YTD],[YTD Total cases],0)

Return

IF(MAX(Date Table [year]) < 2019, "N/A",VC_Ratio)

[selimovd · Accepted solution]

Hey @Anonymous ,

if you want to check for a specific value, you should use the SELECTEDVALUE function.

So you can check if [Case_origin group] or if the selected year is 2019 and then return "N/A" or otherweise return the value:

YTD visual V/C Ratio =
VAR VC_Ratio =
    DIVIDE(
        [CV_TOTAL_VOLUME_YTD],
        [YTD Total cases],
        0
    )
RETURN
    IF(
        ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || SELECTEDVALUE( myDate[Year] ) = 2019,
        "N/A",
        VC_Ratio
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thanks you @selimovd 

Is there better way I can optimize my code, because im using a text column as my date x axis. I cannot use logical operator to filter in syntax.


This is my current code, i have to select all those 12 months

YoY YTD V/C Ratio Visual =

Var YoY_YTD_VC_Ratio = DIVIDE([YTD V/C Ratio],[LY YTD V/C Ratio],0)-1

Var YoY_YTD_VC_Ratio_visual =

IF(

ISFILTERED(

SALESFORCE_CASES_FCT_20210429[Case_origin group]) ||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "12-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "11-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "10-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "09-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "08-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "07-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "06-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "05-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "04-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "03-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "02-2019"||

SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "01-2019"

,"N/A",YoY_YTD_VC_Ratio)

RETURN

YoY_YTD_VC_Ratio_visual

Best regards
Khanh

[selimovd · Accepted solution]

Hey @Anonymous ,

you could use the "IN" operator:

YoY YTD V/C Ratio Visual =
VAR YoY_YTD_VC_Ratio = DIVIDE( [YTD V/C Ratio], [LY YTD V/C Ratio], 0 ) - 1
VAR YoY_YTD_VC_Ratio_visual =
    IF(
        ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) ||
	SELECTEDVALUE( DIM_DATE[MM-YYYY] ) IN {
                "12-2019",
                "11-2019",
                "10-2019",
                "09-2019",
                "08-2019",
                "07-2019",
                "06-2019",
                "05-2019",
                "04-2019",
                "03-2019",
                "02-2019",
                "01-2019"
            },
        "N/A",
        YoY_YTD_VC_Ratio
    )
RETURN
    YoY_YTD_VC_Ratio_visual

Or check for the year:

YoY YTD V/C Ratio Visual =
VAR YoY_YTD_VC_Ratio = DIVIDE( [YTD V/C Ratio], [LY YTD V/C Ratio], 0 ) - 1
VAR YoY_YTD_VC_Ratio_visual =
    IF(
        ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) ||
        RIGHT( SELECTEDVALUE( DIM_DATE[MM-YYYY] ), 4 ) = "2019",
        "N/A",
        YoY_YTD_VC_Ratio
    )
RETURN
    YoY_YTD_VC_Ratio_visual

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic