How to get Selected Value from the filter when there's no data

[nhuda]

Hello,

I have a requirement to get data for selected filters when there's no data for that selection, which sounds conflicting I think.

So let me explain:

I have a Project Selection dropdown and a date range selector slider as below:

When Project A and a Date Range is selected, it is possible that data do not exist for the date range but prior data (prior to the "From" date selected) exists. 

However, the issue is when there's no data for the date range, I am unable to know what Project and what Date range was selected because the method SELECTEDVALUE('MyTable'[Project]) returns blank and same with the date selection. 

Is there a way to directly fetch these values selected in the Slicers even when there's no data for that selection?

Thank you,

nhuda

[parry2k]

@nhuda I can go on explaining things but what I said earlier is the basics. Good luck!

[parry2k]

@nhuda don't take me wrong but  you are incorrect in your replace and your understanding of how Power BI works. 

Let me try to explain, on a single table if you write an SQL statement with where condition that doesn't return a row and at the same time you are asking it to show me the project name.

At the same time if you have a separate project table and set relationship with your main table, you can have left join and with where condition you can still get the project name from the project table even if the main table doesn't have the row for it.

The same is what Power BI doing. There is a lot more to it but going to keep it to basics.

[nhuda]

Hi @parry2k ,

Thank you for the explanation, it's easily understandable.

It's just that power bi won't have to be limited in these kind of situations when we have all these separate objects. The fact that something was selected in a slicer should have allowed the persistence and access/retrieval of that value in some form despite no data returned. It's not just a query, if it was we could have just used tables and SQL in a DBMS environment. PowerBi is a visual interface with all these objects to help with the visualization.

But instead, it behaves like - ok, your query didn't return anything, so I don't know what parameter you were trying to use for the query.

I mean that's how I feel, may be my expectation is unreasonable.

But it is what it is, so looks like I'll have to create other tables and relationships etc. and see if that solves my issue.

Regards,

nhuda

[parry2k]

@nhuda not with the current setup you have. 

[nhuda]

Thank you for your time and suggestion @parry2k. Not sure why microsoft did not make it available, it does not make any sense. 

Even with your setup you suggested I doubt it would behave any better when there's no data (I already have a separate table for something else and with relationship with the main table).

[parry2k]

@nhuda That is the problem. As a best practice, you should create a project dimension and a calendar table and set a relationship with the fact tables, and then use these new tables from the slicer. Read more here Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

[nhuda]

Hi @parry2k ,

Thanks for the suggestion.

However, does that mean that there's no way to get the selections info out of the Slicers?

Regards,

nhuda

[parry2k]

@nhuda Let me clarify one thing - do you have one table that you are using for the project and date slicer?

[nhuda]

Yes @parry2k, one table for them

[nhuda]

Hello @parry2k ,

Thanks for your response.

It works fine, everything is happy when there's data for the date range selected, but when there's no data for the date range, then both the project seelction and date range selections come up as blanks, as a result I can't calculate/count cumulative data that are available for the project that falls outside/prior to the date range, because if I don't know what the selected project and date range is, how can I do anything. There must be a way to directly fetch it from the slicers (despite no data available for the selection).

From the image above,first 3 columns are for selected period, which are 0s (correct, as there's no data for the range selected for the project). But next 3 columns are for cumulative, and they have data.

But as I said, when there's no data for the period, I am not able to know what project or date range was selected because they return blank e.g. SELECTEDVALUE('MyTable'[Project]). That's why counting doesn't work without project or date information.

Hope I am able to clarify.

Regards,

nhuda

[parry2k]

@nhuda not sure if I understood your question, if  a single project is selected then you can use selectedvalue function. For date, you need to use min and max function to understand the what is the range?