DAX MEASURE TO FILTER

[Julier]

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()

)

[ryan_mayu]

could you pls provide some sample data and expected output?

[Julier]

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

FBDC	Month	Target	SITE
11,300	01/07/2025	6,000	BUR
12,401	01/08/2025	6,000	BUR
12,220	01/09/2025	6,000	BUR
13,297	01/10/2025	6,000	BUR
13,297	01/11/2025	6,000	BUR
17,202	01/09/2025	9,000	LIV
17,622	01/10/2025	9,000	LIV
16,142	01/08/2025	9,000	LIV
15,633	01/07/2025	9,000	LIV
18,028	01/11/2025	9,000	LIV

[ryan_mayu]

@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.

[Julier]

Apologies, the radw data target should show 3000 as a target for all machines, I then have 2 measures 

FBDC TARGET = 6000 and 

LIV FBDC Target = 9000 and a table called site with BUR and LIV as a slicer, this gives me the visual above, the performane line changes but the targets dont change on slicer selection which ideally is what i would like to do rather than have indiviudal charts with their individual targets for each.

[Jihwan_Kim]

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.

[Praful_Potphode]

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

[rohit1991]

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]

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 · Accepted solution]

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

[Julier]

FANTASTIC, WORKED LIKE A DREAM !!!!