USERELATIONSHIP + FILTER on the used date columns -> query referenced calculated table Date...

[FatherTheWizard]

Hi

I got error "Query referenced calculated table (dim) Date which does not hold any data and it needs to be refreshed or recalculated." Below details:

I have a sales table where is closing date (date1) and production start date (date2). date1 has physical relationship with Date table and date2 is connected with dotted lines and using USERELATIONSHIP in measures.

I have a measure which uses the USERELATIONSHIP to have the timeline from date2 but I also would like to have a filter which uses calculated columns of both the date columns. Basically the calculated columns are year of date1 and year of date2. I would like the end result to be that Sales figures would have timeline of date2 and that I would only have sales cases where YEAR(date1)=YEAR(date2) - I have applied the logic with filter below and the columns are called year_date1 and year_date2. 

CALCULATE(SUM('(fact) Sales'[invoicing]);USERELATIONSHIP('(dim) Date'[date];'(fact) Sales'[date2]);FILTER('(fact) Sales';'(fact) Sales'[year_date1]='(fact) Sales'[year_date2]))

[FatherTheWizard]

Anybody having experience on above issue?

Basically I am having in same DAX formula USERELATIONSHIP and FILTER which uses two date columns (active and non-active) and getting error on global Date table "not having any data and needs to refreshed or recalculated".

What does that mean?

[FatherTheWizard]

Anyone?

[Anonymous]

If your Date table is a calculated table, this might cause the problem. Maybe it's about circular dependencies under the hood. Create your Date table in Power Query and then import it.

Best advice is this: Stay away from calculated columns and minimize the use of calculated tables. All such things can be done in Power Query. This will keep you safe.

Best
D

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[FatherTheWizard]

@Greg_Deckler  Thanks Greg. After reading that I was still wondering what was the clearest shortfall of presenting my case? Not sure I have seen many clearly better case descriptions but I definitely want to improve but would like to grab the low hanging fruits of what to improve. After that I could repost the case as a reply here.

[Greg_Deckler]

Sample data and expected result from sample data. Really, really shortens the amount of time to understand the problem and get to a solution.

[FatherTheWizard]

@Greg_Deckler Understood. Hopefully the below without .pbix is clear enough.

My "(fact) Sales table" below. Relationships: date1 is connected to regular "(dim) Date" table (date2 is connected only via USERELATIONSHIP)

invoicing	date1	date2	YEAR_date1	YEAR_date2
100	1.12.2019	1.4.2020	2019	2020
200	1.2.2020	1.4.2020	2020	2020

Expected result: Using DAX I would like to see invoicing on a timeline where date2 would be determining the timeline and shows only rows where YEAR_date1=YEAR_date2.

Expected table:

invoicing	Timeline from date2
200	1.4.2020

My Current DAX:

CALCULATE(SUM('(fact) Sales'[invoicing]);USERELATIONSHIP('(dim) Date'[date];'(fact) Sales'[date2]);FILTER('(fact) Sales';'(fact) Sales'[year_date1]='(fact) Sales'[year_date2]))

Current DAX error:

"Query referenced calculated table (dim) Date which does not hold any data and it needs to be refreshed or recalculated."