SUMX with filter using SELECTEDVALUE

[ebbrey]

Hey

Im fairly new to Power BI and im trying to solve SUMX to a non-related table.

The table im trying to get correct number from has info like this:

Elements Table:

Projectnumber	Code2	Start Date	End Date	Demand
12345678	10.10.10	01.01.2019	31.12.2019	6
12345679	10.10.11	15.07.2019	31.12.2019	6
98765432	10.10.10	01.01.2019	15.07.2020	12

In my visuals this table works great, since it filters on Project number. And i dont get issues at all. But i cant pull the data based on project number into power bi, I have to get this list with everything in it.

I have made a calendar that uses First date from Start Date and last date from End date. Then made new colums adding:

Calendar = CALENDAR([FirstDate];[LastDate])

Week = if(WEEKDAY('Calendar'[Date];2)=1;WEEKNUM('Calendar'[Date]);BLANK())
Year = year('Calendar'[Date])

Year-Week= IF('Calendar'[Week]<>BLANK();'Calendar'[YEAR]&"-"&'Calendar'[WEEK];Blank())

Then i removed blank() rows in Week. I need a new column into this calendar table that pulls Demand from elements table so i can show the weeks and what the demand is in that week for the current selected project.

I have tried this so far for a new column:

Demand =
IF('Calendar'[Year-Week]=BLANK();BLANK();

CALCULATE(
sumx(filter('Elements';Elements[Projectnumber]="12345678");Elements[Demand]);

'Elements'[Start Date] <= EARLIER ( 'Calendar'[Date] );

'Elements'[End Date] >= EARLIER ( 'Calendar'[Date] )))

This works. But not based on the current selected project. As i have written the number in. 

i wish this worked:

Demand =
IF('Calendar'[Year-Week]=BLANK();BLANK();

CALCULATE(

sumx(filter('Elements';Elements[Projectnumber]=SELECTEDVALUE(Projectlist[Number]));Elements[Demand]); 

'Elements'[Start Date] <= EARLIER ( 'Calendar'[Date] );

'Elements'[End Date] >= EARLIER ( 'Calendar'[Date] )))

Thanks for any input on how to solve this.

[lc_finance · Accepted solution]

Hi @ebbrey ,

To have the measure based on the currently selected project, you'd need to use a calculated measure instead of a calculated column.

Does this help you?

If it doesn't, can you share a sample Power BI file?

You can share it via One Drive, Google Drive or a similar tool.

Regards,

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[ebbrey]

@lc_finance 

Thanks for your reply to my question and pointing me away from trying to use selectedvalue in a column.

I ended up getting it to work by using a referance table wich i could relate to the project table and get the filter i needed.

[lc_finance]

I'm glad you found a solution for it!

Do not hesitate if you have more questions.

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com