Have a user selected measure filter a visual

[CVance]

I feel like I'm close on this one... I've uploaded a PBIX file with the data and visuals. I'm trying to get the visuals on Page 2 to act like the visuals on Page 1.

Page 1 has a dropdown control (tied to the As of Date in the rate table) to select a yield curve date. Based on that date, the graph will show the points of a yield curve group for that selected date.

Page 2 has a selection of measures that produce an appropriate date. e.g. the "90 days ago" selection will provide a single date that is 90 days prior to the latest "As of Date" in the Rate Data table. The selected date "should" filter the graph, but it does not. I'm having trouble tieing the date produced from the user selection to the As of Date in the Rate Data table.

Page 3 has some visuals for testing. Make a selection and the Yield Curve Date card will show the output of the selected measure. For example, select the Most Recent Yield Curve Date for AAA 1 Month and the Yield Curve Date card will show 9/10/2024.

Link to PBIX file and a DAX Studio file with a working CALCULATETABLE entry that I can't seem to get working in the PBIX file.
https://1drv.ms/f/s!Amj5Ny1VnVSGgleu2pbbpYSiq2jU?e=QPIpw0

Thanks in advance for any ideas or solutions!
P.S. I hope the link works correctly. It's my first time providing a PBIX file.

[v-xiaocliu-msft · Accepted solution]

Hi @CVance ,

Please try this measure

Measure = 
var _date= 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Rate Data Slicer'[Selection]) = "Most Recent Yield Curve Date", CALCULATE( Max('Rate Data'[As of Date]),All('Rate Data')),
    SELECTEDVALUE('Rate Data Slicer'[Selection]) = "90 days ago",   CALCULATE( Max('Rate Data'[As of Date])-90,All('Rate Data')),
    SELECTEDVALUE('Rate Data Slicer'[Selection]) = "180 days ago",CALCULATE( Max('Rate Data'[As of Date])-180,All('Rate Data')),
    SELECTEDVALUE('Rate Data Slicer'[Selection]) = "270 days ago", CALCULATE( Max('Rate Data'[As of Date])-270,All('Rate Data')),
    SELECTEDVALUE('Rate Data Slicer'[Selection]) = "1 year ago", CALCULATE( Max('Rate Data'[As of Date])-365,All('Rate Data'))
)

RETURN CALCULATE(SUM('Rate Data'[Rate Value]),'Rate Data'[As of Date]= _date)

Best Regards,

Wearsky

[lbendlin]

1. Why not use the filter pane?  "Filters on all pages"  gives you the same functionality for free.

2. Once you use the filter pane you only need a single measure.

3. What made you choose CALCULATETABLE over SUMMARIZECOLUMNS or ADDCOLUMNS ?

4. Your calendar table is not marked as a date table

5. Auto Date/Time is not disabled.

[CVance]

1. From what I see, the filter pane does not give me a way to filter by a measure. I can filter to a single date or multiple dates, but they are not tied to a measure that is user selectable.

2. I must not see what you are seeing. Can you provide details? If you have a solution, I would be happy to use it and mark your post as the solution.

3. I tried FILTER and CALCULATETABLE in DAX Studio (file provided), and it gives the correct answer. That's the only reason I chose it.

4. I have a more detailed version where the Calendar table is marked as a Date table. No difference in the outcome. I just marked Calendar as a date table in the file I shared, but no difference in how Page 2 works. 

5. The file on my system shows it as disabled, but I agree the date fields are showing as hierarchical. I tried to "undo" that, but was unsuccessful. I have not had issues with that before, but this file seems to want hierarchical date fields. Is there a setting (other than in File - Options) that I should disable??

Thank you for your comments.