Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating a slicer from LOOKUPVALUE Range

Hello,   I'm trying to create a slicer from the LOOKUPVALUE Range measure, related to my earlier post from last week, which was answered.  Following up on that post, I'm trying to show which campa...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    If i understand what you mean, I think you only need to change the measure into a column, as I wrote above, so that you can use it at any time in the effective table.

     

    Column =
VAR __Score = 'Effective Date and Amount'[Effective Date]
VAR __ID = 'Effective Date and Amount'[Location]
return 
    MAXX (
        FILTER (
            'Campaign Levels',
            [Country Name] = __ID
                && __SCore >= [Campaing Start Date]
                && __Score <= [Campaign End Date]
        ),
        [Campaign Level]
    )

     

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, Anonymous 

 

I try to understand your needs, I think you need to create a column and then put it on the slicer to filter data.

Like this:

Column =
VAR __Score = 'Effective Date and Amount'[Effective Date]
VAR __ID = 'Effective Date and Amount'[Location]
VAR __Scale =
    MAXX (
        FILTER (
            'Campaign Levels',
            [Country Name] = __ID
                && __SCore >= [Campaing Start Date]
                && __Score <= [Campaign End Date]
        ),
        [Campaign Level]
    )
RETURN
    IF ( __Scale <> BLANK (), "has effective date", "no effective date" )

 

If it doesn't solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Many thanks v-janeyg-msft This is lovely. I realized I made a mistake in my original question. I'm actually trying to filter by the LOOUPVALUE Range measure. In other words, I want to create a slicer that separates blank LOOKUPVALUE Range with others, so that it shows whether there is a campaign level on that effective date or not. Apologies for the confusion. 

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Community Support
      5 years ago

      Hi, Anonymous 

       

      This can be done, Can you use pictures or tables to show the results you want?

       

      Best Regards

      Janey Guo

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thanks v-janeyg-msft based on the example dashboard I shared; I created a pie chart as below. As you can see, there are only 4 rows with LOOKUPVALUE Range vs. 11 blank rows. This is the type of filter/pie chart I would like to create, but as mentioned, LOOKUPVALUE Range doesn't allow me to put into a slicer. I'm hoping there would be a relationship between the LOOKUPVALUE Range values with the rest of the table, so I can show the composition of different values. For example. I would be able to see the total value of campaigns when there is a campaign level (LOOKUPVALUE Range is not blank) vs. when there is no campaign level. In relation to that, I would be able to see which locations received campaign contribution while there was a campaign (or vice versa). In below example, 4 locations received while 11 did not. It was just meant to be an example, please ignore if the values don't make much sense.

        Thanks,