Switch function based on selection filter to another table value

[Tevon713]

Hi all, 

Got 2 tables, I want to switch or return value in another table then use that for a measure calculation. 

See below measure created, it not working.

Selected_Measure =
VAR Selection = SELECTEDVALUE (Table[Region])
RETURN
SWITCH ( TRUE(),
Selection = "NE", Table2[Region]="NORTHEAST REGION",
Selection = "SW", Table2[Region]="SOUTHWEST REGION",
Selection = "WEST", Table2[Region]="WEST REGION",
Table2[Region]="ALL"
)

[v-jingzhang]

Hi @Tevon713 

A measure is to display the results in a visual rather than in a data table. You can add below measure into a visual (e.g. a table visual) along with other necessary columns from Table2 to see the result. 

Selected_Measure =
VAR Selection = SELECTEDVALUE ( Table[Region] )
RETURN
    SWITCH (
        TRUE (),
        Selection = "NE", "NORTHEAST REGION",
        Selection = "SW", "SOUTHWEST REGION",
        Selection = "WEST", "WEST REGION",
        "ALL"
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[Tevon713]

Thanks @v-jingzhang.

Tried previously. I'm getting this message when trying to use it return value. Intent is to look for if that account number exist in another table for that selected region.

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Matches Count = CALCULATE(DISTINCTCOUNT(Table2[Acct Num]) , FILTER(Table2,LOOKUPVALUE(Table1[Acct Num],Table1[Acct Num], Table2[Acct Num])), Table2[Region]=[Selected_Measure])

[HiraNegi]

Hi,

To overcome this error, you may also use FILTER function instead of direct filter expression. Filteration in measures does not work directly in expression, but works with FILTER function.

Example: Replace with below.

FILTER(Table2, [Region]=[Selected_Measure])

[v-jingzhang · Accepted solution]

Hi @Tevon713 

This error occurs because Table2[Region]=[Selected_Measure] portion is used as a filter expression in CALCULATE function. To avoid this error, you can try the following measure. Add a variable to get the measure value in advance, then use the value in CALCULATE. 

Matches Count =
VAR __value = [Selected_Measure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table2[Acct Num] ),
        FILTER (
            Table2,
            LOOKUPVALUE ( Table1[Acct Num], Table1[Acct Num], Table2[Acct Num] )
        ),
        Table2[Region] = __value
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!