Forum Discussion
Display measure name based on selected (date slicer) validity dates
Hi guys!
I have a card that should display the name of a measure based on the selected dates and their respective validity ranges (see images for reference). the MeasureNames and the Locations data is located in Sharepoint lists; while the date table was created directly in power BI using:
date = CALENDAR(DATE(2019 , 1 , 1), date( year(TODAY()), MONTH(TODAY()), DAY(TODAY())))
Locations list has the following structure (simplified):
MeasureNames list has the following structure (simplified):
dates table has the following structure:
The idea is to have the names displayed depending on the selected range (date slicer). There are 2 possibilities:
- If the Site, the name, or the selected range is not included on the MeasureNames list, then it should display a default name (e.g. if the user selects date from 01/Jan/2017 to 01/Feb/2017, then the displayed name should be "default)
- If one of the dates selected by the user is included on the MeasureNames list, then it should display the latest name (e.g. if the user selects for the Site MX1 the date range from 01/Feb/2019 to 22/May/2019, then the displayed name should be Eaches IB)
Do you have any ideas as to how to accomplish this? Any ideas are greatly appreciated!
have a great day!
Alejandro
2 Replies
santiagomurResolver II
Try
measure=
SELECTEDVALUE('TABLE'[NAME];"DEFAULT")- Hi Santiago!thanks for your reply.the problem I am facing with the solution you suggested is that the values might be duplicated; i which case, it should show the latest value within the range:Measure=CALCULATE(SELECTEDVALUE(Table[Name],CALCULATE(LASTNONBLANK( Table[Name],1),FILTER(Table,Table[DateFrom] <= [MINDATE] || Table[DateTo] >= [MAXDATE])))Currently, I can´t make PBI filter the table only for the values within the rangeAny suggestions?