Forum Discussion

Julier's avatar
Julier
Helper III
8 months ago
Solved

DAX MEASURE TO FILTER

I have this measure to show a machine target for each site, the conversion machine is the same name at both sites but the target differs by each site so i want the slicer to change the visual and show the correct target by site, can you please let me know where my measure is going wrong as it wont display any numbers on the visual

FBDC MACHINE TARGET =
VAR SelectedSite = SELECTEDVALUE('CONVERSION'[FBDC])
RETURN
SWITCH(
    SelectedSite,
    "BUR", 9000,
    "Liv", 6000,
    BLANK()
)
Need Help
  • Jaywant-Thorat's avatar
    Jaywant-Thorat
    8 months ago

    Hi Julier 

    Nice, good progress. i have gone thru all the posts. Two things are happening here:

    1. Your 'SelectedSite' calculation is more complicated than it needs to be and may return unexpected results (or blank) when more than one site is in context.

    2. If the site variable is blank or different text (case/space), the 'SWITCH' returns 'BLANK( )' and the chart shows no target. Also check that your target measures are numeric (they usually are — but if they return 'BLANK( )' because of filter context that will look like “not seeing” the number).

    If you created a separate 'Site' table and your slicer uses 'Site[SITE]', use 'SELECTEDVALUE' on that column , it’s the simplest and most reliable:

    ----------DAX CODE--------

    Selected Site Target =
    VAR sel = UPPER( TRIM( SELECTEDVALUE( 'Site'[SITE], "" ) ) )
    RETURN
    SWITCH(
    TRUE(),
    sel = "LIV", [LIV FBDC Target],
    sel = "BUR", [FBDC TARGET],
    -- default when no single site selected: return RADW default 3000 (or BLANK() if you prefer)
    3000
    )

    ----------DAX CODE--------

    How this helps:

    • 'SELECTEDVALUE' returns the single slicer value (or ' "" ' when multiple/none).

    • 'TRIM' and 'UPPER' normalize the text.

    • The 'SWITCH(TRUE(), ...) pattern works well for string comparisons.

    If you prefer to show nothing until the user picks a single site, replace '3000' with 'BLANK( )'.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!

    Appreciate your Kudos!!

    Jaywant Thorat | MCT | Data Analytics Coach
    Linkedin: https://www.linkedin.com/in/jaywantthorat/
    Join #MissionPowerBIBharat = https://shorturl.at/5ViW9

     

10 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    8 months ago

    Hii Julier 

     

    Your measure is returning blank because SELECTEDVALUE('CONVERSION'[FBDC]) is not getting a single site value from the slicer. Make sure the slicer is built from the same column you are using in SELECTEDVALUE. The DAX itself is fine once the slicer filters the column to one value, the SWITCH will return the correct target. Example:

    FBDC MACHINE TARGET =
SWITCH(
    SELECTEDVALUE('CONVERSION'[FBDC]),
    "BUR", 9000,
    "LIV", 6000,
    BLANK()
)

    If the slicer isn’t connected (no relationship / wrong column), the measure will stay blank.

    • Julier's avatar
      Julier
      Helper III
      8 months ago

      I  have added a table called site which now dynamically changes the performacne line and now have this measure which does not seem to see the targets as a number, when I put the target number for each indiviudal site in it shows on the chart however when using this measure it does not seem to see it

      Selected Site Target =
      VAR SelectedSite =
          MAXX(
              VALUES('CONVERSION'[FBDC]),
              UPPER(TRIM('CONVERSION'[FBDC]))
          )
      RETURN
      SWITCH(
          TRUE(),
          SelectedSite = "LIV", [LIV FBDC Target],
          SelectedSite = "BUR", [FBDC TARGET],
          BLANK()
      )
      • Jaywant-Thorat's avatar
        Jaywant-Thorat
        Super User
        8 months ago

        Hi Julier 

        Nice, good progress. i have gone thru all the posts. Two things are happening here:

        1. Your 'SelectedSite' calculation is more complicated than it needs to be and may return unexpected results (or blank) when more than one site is in context.

        2. If the site variable is blank or different text (case/space), the 'SWITCH' returns 'BLANK( )' and the chart shows no target. Also check that your target measures are numeric (they usually are — but if they return 'BLANK( )' because of filter context that will look like “not seeing” the number).

        If you created a separate 'Site' table and your slicer uses 'Site[SITE]', use 'SELECTEDVALUE' on that column , it’s the simplest and most reliable:

        ----------DAX CODE--------

        Selected Site Target =
        VAR sel = UPPER( TRIM( SELECTEDVALUE( 'Site'[SITE], "" ) ) )
        RETURN
        SWITCH(
        TRUE(),
        sel = "LIV", [LIV FBDC Target],
        sel = "BUR", [FBDC TARGET],
        -- default when no single site selected: return RADW default 3000 (or BLANK() if you prefer)
        3000
        )

        ----------DAX CODE--------

        How this helps:

        • 'SELECTEDVALUE' returns the single slicer value (or ' "" ' when multiple/none).

        • 'TRIM' and 'UPPER' normalize the text.

        • The 'SWITCH(TRUE(), ...) pattern works well for string comparisons.

        If you prefer to show nothing until the user picks a single site, replace '3000' with 'BLANK( )'.

         

        Did I answer your question? Mark my post as a solution! This will help others on the forum!

        Appreciate your Kudos!!

        Jaywant Thorat | MCT | Data Analytics Coach
        Linkedin: https://www.linkedin.com/in/jaywantthorat/
        Join #MissionPowerBIBharat = https://shorturl.at/5ViW9

         

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    8 months ago

    Hi Julier ,

    Please try below:

    FBDC MACHINE TARGET =
VAR SelectedSite = SELECTEDVALUE('CONVERSION'[FBDC])
VAR FilterCount = COUNTROWS(FILTERS('CONVERSION'[FBDC]))
RETURN
IF(
    FilterCount = 1 && NOT(ISBLANK(SelectedSite)),
    SWITCH(
        SelectedSite,
        "BUR", 9000,
        "Liv", 6000,
        BLANK()
    ),
    BLANK()
)

    HASONEVALUE checks if one value is selected from slicer.

    please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    8 months ago

    Hi,

    I am not 100% sure but your measure looks OK.

    Please check the below picture and the attached pbix file.

    I tried to create a sample data model like below. 

    Please check your data model how it looks like.

     

     

     

     

    • Julier's avatar
      Julier
      Helper III
      8 months ago

      I  have added a table called site which now dynamically changes the performance line and now have this measure which does not seem to see the targets as a number, when I put the target number for each indiviudal site in it shows on the chart however when using this measure it does not seem to see it

      Selected Site Target =
      VAR SelectedSite =
          MAXX(
              VALUES('CONVERSION'[FBDC]),
              UPPER(TRIM('CONVERSION'[FBDC]))
          )
      RETURN
      SWITCH(
          TRUE(),
          SelectedSite = "LIV", [LIV FBDC Target],
          SelectedSite = "BUR", [FBDC TARGET],
          BLANK()
      )

       

      Here is my sample data and how i would like the chart to look but be able to change dynamically using a slicer
      FBDCMonthTargetSITE
              11,30001/07/2025          6,000BUR
              12,40101/08/2025          6,000BUR
              12,22001/09/2025          6,000BUR
              13,29701/10/2025          6,000BUR
              13,29701/11/2025          6,000BUR
              17,20201/09/2025          9,000LIV
              17,62201/10/2025          9,000LIV
              16,14201/08/2025          9,000LIV
              15,63301/07/2025          9,000LIV
              18,02801/11/2025          9,000LIV
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        8 months ago

        Julier  

        i think you need to calculate the total target and does not calculate the target separately. Then we can use site column to filter to show different site's value. 

         

        what's more, do you have other tables? since the raw data you provided can't get the visual that you provided.