cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AbdelrahmanAyad
Frequent Visitor

Use The date selected in the page filter to use it as a filter in another query

Hey Guys , I have a requirement and I am somehow new to powerbi. 

I am doing a dashboard for a constructions company. The data model is as below

Img1.png

 

This is how the data in the project progress looks like.

Img2.png

The user selects the As of date in powerbi from a list. 

So I want to check the projects that have status Active at this date and use these project ref to filter all my calculations. I came up with a DAX formula as follow : 
Measure X = CALCULATE(SUM('Approved Variations'[Amount]), FILTER('Approved Variations' , 'Approved Variations'[Project Ref] IN (

VAR M = FILTER('Project Progress', 'Project Progress'[Project Status] <> "Non-Active" && 'Project Progress'[As of Date] = SELECTEDVALUE('Calendar'[Date]))
RETURN SELECTCOLUMNS(M,"Project Ref", 'Project Progress'[Project Ref])

When I use selected values it does not work however if I replaced SelectedValue with a static value like this one : DATE(2017/12/31) It works So any clues or ways to let this work?? 

 

Thank You very much

1 REPLY 1
v-yulgu-msft
Microsoft
Microsoft

Hi @AbdelrahmanAyad,

 

Have you tried my suggestion in an older post? Does it work in your scenario?

 

By the way, do mask sensitive data before uploading, including your personal account.

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.