Forum Discussion
Help with SELECTEDVALUE
I'm trying to return data in the 'HELP' table so that when the 'Reporting View' slicer is:
Day Selected - the table returns data for the date selected in the 'Date' slicer only
PTD - the table returns data for the whole period up to and including the date in the 'Date' slicer
A filter has been applied to the 'Help' table which is '3) measure for filter' to enable either a result of '1' for 'Reporting View' of 'Day Selected' or '2' for PTD.
The problem is that when the Reporting View PTD is selected the table only returns the date selected and not all of the entries in the period i.e. from the 9th Jan - 19th Jan. You can see in the 'DATA' table (to the right) that the '3) Measure for filter' is working and providing a '2' if the PTD slicer is chosen.
If the relationship is 1:1 then I don't get this issue however I need the relationship to be many : 1 or 1: many and when this relationship is chosen I get this error.
Any ideas??
Code below:
1) Measure to return Day Selected =
Var p=CALCULATE(max(Date_Period_Table[Period]),ALL(Date_Period_Table),Date_Period_Table[Date]=SELECTEDVALUE('Date'[Date]))
return
IF(SELECTEDVALUE('Reporting View'[Reporting View]) = "Day Selected",
IF(SELECTEDVALUE(Date_Period_Table[Period])=p && SELECTEDVALUE(Date_Period_Table[Date])=SELECTEDVALUE('Date'[Date]),1,0)
)2) Measure to return PTD =
Var p=CALCULATE(max(Date_Period_Table[Period]),ALL(Date_Period_Table),Date_Period_Table[Date]=SELECTEDVALUE('Date'[Date]))
return
IF(SELECTEDVALUE('Reporting View'[Reporting View]) = "PTD",
IF(SELECTEDVALUE(Date_Period_Table[Period])=p && SELECTEDVALUE(Date_Period_Table[Date])<=SELECTEDVALUE('Date'[Date]),1,0)
)3) Measure for filter =
IF([Measure Day Selected]=SELECTEDVALUE('Reporting View'[Reporting View]),1,
IF([Measure PTD]=SELECTEDVALUE('Reporting View'[Reporting View]),2,0))
File link below:
https://drive.google.com/file/d/14UJZFO0OEi3l6tdMeI3VMHvvgm3tsGu1/view?usp=sharing
Hi MWare ,
According to your description, I download your pbix. Here's my solution, modify the formula like this:
Measure PTD = VAR p = MAXX ( FILTER ( ALL ( Date_Period_Table ), Date_Period_Table[Date] = SELECTEDVALUE ( 'Date'[Date] ) ), Date_Period_Table[Period] ) RETURN IF ( SELECTEDVALUE ( Date_Period_Table[Period] ) = p && SELECTEDVALUE ( Date_Period_Table[Date] ) <= SELECTEDVALUE ( 'Date'[Date] ), "PTD" )Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
MWare , Not very clear, if you select one date and want to show trend data on more than one date, then you need an independent table
Example MTD showing all 19 dates
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))if just want to show data for 19 days
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sSorry if it was not very clear that is why I provided the link to the pbix file as hopefully it's easier to see what is going on.
For clarity there is already an independant date table in the pbix file. The issue is not just returning a calculated value but instead returning entries (values and text columns) from a table based on a date in the slicer. Also the periods are custom periods and therefore not simply months, the custom periods are part of and included in the main date table (not the independant date table).
In this example my custom period runs from 9th Jan - 5th Feb. If a user selects the 19th Jan from the independant date slicer and selects 'Day Selected' in the Reporting View slicer then I'd just expect the results from 19th Jan. If however the user selects the 19th Jan and also 'PTD' in the reporting view slicer then the results I'd expect to see would be all records from the 9th - 19th Jan.
If you open the pbix and amend the relationship to 1:1 for the only relationship that exists and then select 'PTD' in the reporting view slicer it will return the results similar to what I'm expecting. The issue is that I have 1:many and when this relationship is selected then the results do not return as I'd expect to.
Hi MWare ,
According to your description, I download your pbix. Here's my solution, modify the formula like this:
Measure PTD = VAR p = MAXX ( FILTER ( ALL ( Date_Period_Table ), Date_Period_Table[Date] = SELECTEDVALUE ( 'Date'[Date] ) ), Date_Period_Table[Period] ) RETURN IF ( SELECTEDVALUE ( Date_Period_Table[Period] ) = p && SELECTEDVALUE ( Date_Period_Table[Date] ) <= SELECTEDVALUE ( 'Date'[Date] ), "PTD" )Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yanjiang-msft Many thanks for taking the time to download the sample file and solving my issue - top kudos!
