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 filters applied via slicer), then use this list to filter the same table and count all event type for each DEAL_ID.

 

The aim is to get the below function working dynamically, using the VAR list instead of the array of strings:
----------------------------------------------------------------------------------------------------------------

VERSION 1

Weekly Ops =
VAR list = FILTER(VALUES(F_Events_Sequence[DEAL_ID]),[S_OPPORTUNITY_FLAG]=1)
VAR DealCount = CALCULATE(
DISTINCTCOUNT(F_Events_Sequence[DEAL_ID]),
ALL(F_Events_Sequence),
F_Events_Sequence[DEAL_ID] IN {"7215145373","7215072103","7214289459","7214323974","7204440736","4079127350"},
)

RETURN
DealCount
----------------------------------------------------------------------------------------------------------------

VERSION 2

Weekly Ops =
VAR list = FILTER(VALUES(F_Events_Sequence[DEAL_ID]),[S_OPPORTUNITY_FLAG]=1)
VAR DealCount = CALCULATE(
DISTINCTCOUNT(F_Events_Sequence[DEAL_ID]),
ALL(F_Events_Sequence),
F_Events_Sequence[DEAL_ID] IN list,
)

RETURN
DealCount
 ----------------------------------------------------------------------------------------------------------------
 
 When I use the VERSION 1 formula as the values in a matrix with Deal_ID as rows and Event as columns, it works perfectly, returning all event types for each deal.
 
However, when I use VERSION 2 it only returns the event OPPORTUNITY for each deal.
 
Please can you help? Thanks in advance.
 
 
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

7 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    SBaher,

     

    Will this work?

     

    Weekly Ops =
CALCULATE (
    DISTINCTCOUNT ( F_Events_Sequence[DEAL_ID] ),
    ALL ( F_Events_Sequence ),
    [S_OPPORTUNITY_FLAG] = 1
)

     

  • 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