Forum Discussion

SBaher's avatar
SBaher
Regular Visitor
4 years ago
Solved

VALUES function not working - using the resulting array of values works ok

Hi,   I am struggling with the VALUES function in the first part of the formula below. I am trying to get a list of unique DEAL_IDs where the Opportunity event happend in any given week (date filte...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi SBaher ,

     

    Check the formula.

    Measure = 
var _tmp1 = CALCULATETABLE(VALUES('Table'[Deal_ID]),FILTER(ALL('Table'),'Table'[EOW] = SELECTEDVALUE(slicer[EOW])))
var _tmp2 = CALCULATETABLE(VALUES('Table'[Event]),FILTER('Table','Table'[Deal_ID] in _tmp1))
return
IF(SELECTEDVALUE('Table'[Event]) in _tmp2,1,BLANK())

     

    Pbix as attached.

     

    Best Regards,

    Jay

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi SBaher ,

 

Why using VALUES() function?

Check if the below formula works.

Measure = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER('Table',[_flag]=1))

If you still want use VALUES() function, refer this formula.

Measure 2 =
var _list = CALCULATETABLE(VALUES('Table'[id]),FILTER('Table',[_flag]=1))
return
CALCULATE(DISTINCTCOUNT('Table'[id]),ALL('Table'),'Table'[id] in _list)
 
Best Regards,
Jay
  • SBaher's avatar
    SBaher
    Regular Visitor
    4 years ago

    Hi,

     

    Thank you for looking into this, but unfortunately this still doesn't work in my case, hence it should not have been accepted as a solution.

     

    Thanks,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi SBaher ,

       

      Please show some sample data and expected result so that we could test the formula for you.

       

      Best Regards,

      Jay

      • SBaher's avatar
        SBaher
        Regular Visitor
        4 years ago

        Hi Jay,

         

        This is the original data showing Deal ID, Date, Event, and Opportunity flag (which is basically 1 when Event = "Opportunity"):

         

         

         

        I then have a WeekEnding slicer in my report. The objective is that, when I select any specific WeekEnding, I want to get the list of deals where the event Opportunity happened on that week.

        Then for that list of deals, I want to see which events they had, not just on the selected week but all time.

        So in the scenario of selected WeekEnding = 12/12/2021 the outcome should be:

         

        Event 1

        Event 2

        Event 3

        Event 4

        Event 5

        Event 6

        Opportunity

        Deal C

        1

        1

         

        1

        1

        1

        1

        Deal D

        1

        1

         

        1

        1

        1

        1

        Deal E

        1

        1

         

        1

        1

        1

        1

         

        On WeekEnding = 13/06/2021 the outcome should be:

         

        Event 1

        Event 2

        Event 3

        Event 4

        Event 5

        Event 6

        Opportunity

        Deal B

        1

        1

         

        1

         

        1

        1

         

        On WeekEnding = 20/06/2021 the outcome should be:

         

        Event 1

        Event 2

        Event 3

        Event 4

        Event 5

        Event 6

        Opportunity

        Deal A

        1

         

         

         

         

         

        1

         

        Thanks in advance for looking into this,

        SBaher