Filter a visual with two calendar tables and no relationship

[MCacc]

Hello everyone, 

let's say I have this situation in PowerBi: 

Filter Year and the two Date filters are in a relationship, but because these two calendars works for 19 tables with millions of records, I'd like to avoid relationships between fatcs tables and calendar tables + related functions and so on. 

Is there any way I can possibily make this visual work? Meaning:

1) I select one year,

2) I select one date from Data From filter

3) I have all the corresponding dates in Date To filter

The table will behave accordingly with the selection. 

Thanks a lot

I hope my issue is clear. 

Thank you.

[MCacc]

oh and also, I'm not sure it can work because it says "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[MCacc]

Thank you for your input but:

1) From the fact table I have two dates, Date From and Date To as well. So, is there any way to make it work with two dates? That's my problem

2) I'm not sure how I'm supposed to use this measure, should I turn it into a flag?

Thanks again for your help 🙂

[rajendraongole1]

Hi @MCacc - you can try with measures using selected value for year and allselected for datefrom and dateto

Create measures to capture the selected dates from the slicers:

SelectedYear = SELECTEDVALUE(DateTable[Year])

SelectedDateFrom = MINX(ALLSELECTED(DateTable), DateTable[Date])

SelectedDateTo = MAXX(ALLSELECTED(DateTable), DateTable[Date])

Create measures to filter the data in your visuals based on the selected dates:

FilteredData =
CALCULATETABLE(
FactTable,
FactTable[Date] >= [SelectedDateFrom] && FactTable[Date] <= [SelectedDateTo]
)

i hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!