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
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
VERSION 2
Solved! Go to 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())
Pbix as attached.
Best Regards,
Jay
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.
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
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
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"):
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:
On WeekEnding = 13/06/2021 the outcome should be:
On WeekEnding = 20/06/2021 the outcome should be:
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())
Pbix as attached.
Best Regards,
Jay
Will this work?
Weekly Ops =
CALCULATE (
DISTINCTCOUNT ( F_Events_Sequence[DEAL_ID] ),
ALL ( F_Events_Sequence ),
[S_OPPORTUNITY_FLAG] = 1
)
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
167 | |
136 | |
134 | |
100 | |
86 |