Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SBaher
Regular Visitor

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.
 
 
1 ACCEPTED SOLUTION

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

 1.PNG2.PNG

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

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)
Capture.PNG
 
Best Regards,
Jay
Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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,

 

Hi @SBaher ,

 

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

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi Jay,

 

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

 

SBaher_0-1642590169121.png

 

 

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

 

Hi Jay,

 

Sending again in case you cannot see the outcome tables properly in my previous message.

 

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

 

SBaher_1-1642590541737.png

 

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:

SBaher_2-1642590558739.png

 

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

SBaher_3-1642590578661.png

 


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

SBaher_4-1642590597093.png

 

Thanks in advance for looking into this,

SBaher

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

 1.PNG2.PNG

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
DataInsights
Super User
Super User

@SBaher,

 

Will this work?

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.