Problem with 'PLACEHOLDER' in filteredData and Multiple values error in the calendar table

[poko]

Hello everyone, I would like to ask you for a help in my little project. As I'm new in Power BI and beginner,  I experience two

Please help me with with this proble I appreciate your solution, rewrite it, change, replace anything you like in there that will help me to make it work. Please upload your fixed version here.

[v-zhengdxu-msft · Accepted solution]

Hi @poko 

Question 1:

Just as the CALENDAR() function said that the paramenters should be the single value 

But the DATEADD() function returns a table(mutipal values), so these two function can not be used nested.

Here I did some change on your measures:

SelectedDate = MAX(Calendar[Date])

DateThreshold = DATE(YEAR([SelectedDate]) - 5, MONTH([SelectedDate]), DAY([SelectedDate]))

The DateThreshold returns:

Then add a measure:

Stand_5Y =
VAR _DateThreshold = [DateThreshold]
RETURN
    CALCULATE (
        COUNT ( 'CecLeadingCase'[CecCaseId] ),
        FILTER ( 'Calendar', 'Calendar'[Date] <= _DateThreshold )
    )

The result is as follow:

Question 2:

If you just want to filter the data, you can just change the dax of calculated table as:

FilteredData =
FILTER (
    CecCaseDetail,
    CecCaseDetail[DefinitiveJudgmentDate] <= [DateThreshold]
        && CecCaseEvent[FromStateOfProceedingID] = 3
        && CecCaseEvent[ToStateOfProceedingID] = 3
)

But it's worth noting that the it will returns this error:

This is caused of your relationship(the cross-filter direction is single and the direction is from CecCaseDetail to CecCaseEvent so that you can not use fields in CecCaseEvent to filter CecCaseDetail):

You can change the direction into both(it has some limitations) or filter the table manually, here's a link for your reference:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @poko ,

The Calendar function needs a single value on the calculation when you do the DATEADD function using the Column you are getting several values in this case you need to pickup up the min or maximum date something like:

Calendar1 = CALENDAR(MAXX(DATEADD(CecCaseDetail[DefinitiveJudgmentDate], -5, YEAR), CecCaseDetail[DefinitiveJudgmentDate]), TODAY())

On the second case you are using a Measure to calculate a table and this is also not possible because measures are context driven so wihtout any context coming from the report there is no calculation.

On top of that the speficic metric you defined has an error because because you are using the incorrect syntax.

For this case I would sugest that you would use the calendar syntax but with a change:

Calendar1 = 
var maximumdate = YEAR(MAX(CecCaseDetail[DefinitiveJudgmentDate]))
Return
CALENDAR(DATE(maximumdate - 5, 1, 1), TODAY())

In this case I'm using the maximum date for the judment but you can also pickup the minimum for example.

I'm also forcing the calendar to start on the 1 of January because of best practice but if you want the same day has in the date just redo to this:

Calendar1 = 
var maximumdate = MAX(CecCaseDetail[DefinitiveJudgmentDate])
Return
CALENDAR(DATE(YEAR(maximumdate) - 5,MONTH(maximumdate), DAY(maximumdate)), TODAY())

[poko]

It fit in calendar and no error but criteria doesnt work. It doesnt show historic data e.g user pick 11/10/2024 and it should show data from 11/10/19 and older. Can you check it with my pbix files? 

[v-zhengdxu-msft · Accepted solution]

Hi @poko 

Question 1:

Just as the CALENDAR() function said that the paramenters should be the single value 

But the DATEADD() function returns a table(mutipal values), so these two function can not be used nested.

Here I did some change on your measures:

SelectedDate = MAX(Calendar[Date])

DateThreshold = DATE(YEAR([SelectedDate]) - 5, MONTH([SelectedDate]), DAY([SelectedDate]))

The DateThreshold returns:

Then add a measure:

Stand_5Y =
VAR _DateThreshold = [DateThreshold]
RETURN
    CALCULATE (
        COUNT ( 'CecLeadingCase'[CecCaseId] ),
        FILTER ( 'Calendar', 'Calendar'[Date] <= _DateThreshold )
    )

The result is as follow:

Question 2:

If you just want to filter the data, you can just change the dax of calculated table as:

FilteredData =
FILTER (
    CecCaseDetail,
    CecCaseDetail[DefinitiveJudgmentDate] <= [DateThreshold]
        && CecCaseEvent[FromStateOfProceedingID] = 3
        && CecCaseEvent[ToStateOfProceedingID] = 3
)

But it's worth noting that the it will returns this error:

This is caused of your relationship(the cross-filter direction is single and the direction is from CecCaseDetail to CecCaseEvent so that you can not use fields in CecCaseEvent to filter CecCaseDetail):

You can change the direction into both(it has some limitations) or filter the table manually, here's a link for your reference:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[poko]

Hello, Thank you for solution but still getting this error you mentioned above. I changed relationship between tables to "Both" and still "filteredData" doesnt work. Where do you could be the problem?

or this similar  error

[poko]

I get this error as well

[poko · Accepted solution]

Ok this problem was solved if you get this error, check again relationships between tables