SeletedValue function with Date Filter not displaying properly.

[rtaylor]

Hello and Thanks in Advance!

I created a measure using selectedvalue function to display a category as a title. The problem is whenever I select any month to filter by, that isn't the first month within the year it displays an the if error portion of the formula "OHA Finances". Please see the measure below

-----------------------------------------------------------------------------

VarianceReport_Monthly1 =

Var DateRangemin = CALCULATE(min('Date'[Date]))

Var DateRangemax = CALCULATE(max('Date'[Date]))

Var filtername = SELECTEDVALUE(BudgetMap[Budget Summary Ovrhead],"OHA Finances")

Var reportrange = filtername & " - " & DateRangemin & " to " & DateRangemax

Return

reportrange

--------------------------------------------------------

The result of the measure should be something like this:

Academic Year License Agreements - 7/1/2019 to 6/30/2020

When selecting another month aside from starting in July I get the error result

OHA Finances - 8/1/2019 to 8/31/2019

Overall

The results are as follows

July(1st of fiscal year) filter shows correctly

August and forward filter shows incorrectly

July selected and forward filter shows correctly

I have no idea why this syntax doesn't work.

I've published an example report below for easy viewing

https://app.powerbi.com/view?r=eyJrIjoiZWViOTA5ZWItYzI5Zi00ZGMwLTk3N2EtZjEwNjE3MGY0MWQyIiwidCI6Ijk2N...

[rtaylor]

Posting Fix. This thread solution has not been marked, because we don't have answer to why this change in formula is required

VarianceReport_Monthly1 =

Var DateRangemin = CALCULATE(min('Date'[Date]))

Var DateRangemax = CALCULATE(max('Date'[Date]))

Var filtername = Calculate(SELECTEDVALUE(BudgetMap[Budget Summary Ovrhead],"OHA Finances"),ALLEXCEPT('Date','Date'[Date]))

Var filternameX = CONCATENATEX(VALUES(BudgetMap[Budget Summary Ovrhead]),BudgetMap[Budget Summary Ovrhead],",")

Var reportrange = filtername & " - " & DateRangemin & " to " & DateRangemax

Return

reportrange

[d_gosbell]

---

@rtaylor wrote:

Posting Fix. This thread solution has not been marked, because we don't have answer to why this change in formula is required

Var filtername = Calculate(SELECTEDVALUE(BudgetMap[Budget Summary Ovrhead],"OHA Finances"),ALLEXCEPT('Date','Date'[Date]))

What the calculate is doing here is to ignore the filters on the date slicers. Effectively returning the value from [Budget Summary Overhead] for July. 

You still have an issue in your data. If you de-select "Labour" when you have "Sep" selected you will notice that the [Budget Summary Overhead] slicers will go completely empty. This is because there is no data for this column for Sep, which is why selectedvalue() is returning blank.

We don't need a model with real data, but if you could supply a sample model with just the 2-3 tables in question and 5-10 made up rows (that still demostrate this problem) we could diagnose the root cause of this issue. Without being able to see the relationship structure and what the data looks like it is really hard to help.

[v-lili6-msft]

hi @rtaylor 

For your case, it is because that there is no [Budget Summary Ovrhead] data in Aug 2019.

If you want it show Academic Year License Agreements - 8/1/2019 to 8/31/2019 instead of OHA Finances - 8/1/2019 to 8/31/2019 in your report although there is no [Budget Summary Ovrhead] data in Aug 2019, you could try this way to adjust your data model.

Step1:

Create a separate dim Budget Summary Ovrhead table by this formula

Dim Budget Summary Ovrhead = VALUES(BudgetMap[Budget Summary Ovrhead])

Step2:

Create the relastionship with BudgetMap table as below:

Note: cross filter direction is "Single"

Step3:

Use Budget Summary Ovrhead field from this new dim table as a slicer

Step4:

Adjust the VarianceReport_Monthly1 formula as below:

VarianceReport_Monthly1 =

Var DateRangemin = CALCULATE(min('Date'[Date]))

Var DateRangemax = CALCULATE(max('Date'[Date]))

Var filtername = SELECTEDVALUE('Dim Budget Summary Ovrhead'[Budget Summary Ovrhead],"OHA Finances")

Var reportrange = filtername & " - " & DateRangemin & " to " & DateRangemax

Return

reportrange

Regards,

Lin

[d_gosbell]

It's hard to say without being able to see your data model. But it looks like you have some sort of bi-directional filtering going on and you only have data for Jul 2019, so if Jul 2019 is not selected the slicer has no values hence SELECTEDVALUE() returns it's alternate value. Changing the bi-directional filtering to single directional might fix this (although it could cause other issues depending on how you have modelled your data)

[rtaylor]

Hello,

Actually I do have data up until december 2019.

I wish I could share the model, but I'd have to get approval from far too many people.

So yeah, I have work around developed, but I'm trying to understand why this won't work. As of right now this measure is breaking my basic understanding of how filter/slicers are applied.

[v-lili6-msft]

hi @rtaylor 

Please see this screenshot, after i reset all the slicer and then select Fiscal Year 2019,Month Aug, Now there is no data in [Budget Summary Ovrhead] for choose. This means that there is no data.

You said that you do have data up until december 2019, if you mix up the fiscal date and calendar date in the model? or please check that if it is indeed that there is data in fiscal date aug 2019 in your power bi report.

If you still have problem, please share your sample pbix file for us have a test. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Lin

[rtaylor]

Hello,

Thanks for you reply I updated the sample published report to allow for some fake actuals/masked actuals via card. I still can't post a sample pbix file.

You can see that if I sort by Budget Overhead first, I'm able to sort by month and get different values based on the month I've chosen.

I would use labor as there is guaranteed values for each month.

I just don't understand why selectedvalue fails.

https://app.powerbi.com/view?r=eyJrIjoiZWViOTA5ZWItYzI5Zi00ZGMwLTk3N2EtZjEwNjE3MGY0MWQyIiwidCI6Ijk2N...

[rtaylor]

These pictures are posted for proof of issue. Taken from

https://app.powerbi.com/view?r=eyJrIjoiZWViOTA5ZWItYzI5Zi00ZGMwLTk3N2EtZjEwNjE3MGY0MWQyIiwidCI6Ijk2N...

[rtaylor]

Thank you for the reply. There is data in the Aug 2019. I can filter that data no problem. Its just with this particular measure nothing shows up.

Do you know if there is a filter/slicer heirarchy could be affecting the model?

Like if I sort by category, and then apply the date filter, I lose the category filter?