Forum Discussion

Tevon713's avatar
Tevon713
Helper V
3 years ago
Solved

Switch function based on selection filter to another table value

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

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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!

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    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's avatar
      Tevon713
      Helper V
      3 years ago

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

       

       

       

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        3 years ago

        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!