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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SBaher
New Member

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors