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