Forum Discussion
VALUES function not working - using the resulting array of values works ok
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