Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to use ISFILTERED with condition

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...
Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    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
     
  • selimovd's avatar
    selimovd
    5 years ago

    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
     
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

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