Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
poko
Helper III
Helper III

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

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 problems due to lack of skills and experiences. 

The first problem...

I am having problem with the calendar "A table of multiple values was supplied where a single value was expected.".

 

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

 

I am having problem with the calendar "A table of multiple values was supplied where a single value was expected.".

 

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

 

poko_0-1728633778122.png

 

 

The second problem......

I have this problem "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

FilteredData = CALCULATETABLE(CecCaseDetail,

CecCaseDetail[DefinitiveJudgmentDate] <= [DateThreshold],

CecCaseEvent[FromStateOfProceedingID] = 3,

CecCaseEvent[ToStateOfProceedingID] = 3)

 

poko_1-1728633960153.png

 

This error appears same here when I tried the second approach, a different way to work it with the calendar.

FilteredData =
CALCULATETABLE(
CecJudgement,
CecCaseDetail[DefinitiveJudgmentDate] <= EDATE(CalendarTable[SelectedDate2], -60),
CecCaseEvent[FromStateOfProceedingID] = 3,
CecCaseEvent[ToStateOfProceedingID] = 3
)

 

 

I attached pbix files. I tried to make it work two different ways ,all is the same just FilteredData or the calendar code changed.  Please you must look into Power BI files to see where is exactly the problem.

 

The EXPECTED RESULT: 

I need 5 years old data from the current date. It means when an user select 09/10/2024 in the calendar then the result will shows data from 09/10/2019 and older(not just a one day but all), No slicer in between dates, the calendar slicer must be only with a one date for a user, please see in pbix file.

Also, I implemented in "FilteredData" criteria "3". It has to match in both columns for CecCaseEvent[FromStateOfProceedingID] = "3" and CecCaseEvent[ToStateOfProceedingID] = "3"  and shows data 5 years older. 

 

Attached pbix files:

https://easyupload.io/drlxz2

https://easyupload.io/1vy7ra

 

 

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.

 

2 ACCEPTED SOLUTIONS

Hi @poko 

 

Question 1:

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

vzhengdxumsft_0-1728883835612.png

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

vzhengdxumsft_1-1728883911581.png

Here I did some change on your measures:

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

The DateThreshold returns:

vzhengdxumsft_2-1728885850414.png

Then add a measure:

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

The result is as follow:

vzhengdxumsft_3-1728885940076.png

 

 

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:

vzhengdxumsft_0-1728876219159.png

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):

vzhengdxumsft_1-1728876338654.png

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.

View solution in original post

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

View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

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())




Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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? 

Hi @poko 

 

Question 1:

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

vzhengdxumsft_0-1728883835612.png

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

vzhengdxumsft_1-1728883911581.png

Here I did some change on your measures:

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

The DateThreshold returns:

vzhengdxumsft_2-1728885850414.png

Then add a measure:

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

The result is as follow:

vzhengdxumsft_3-1728885940076.png

 

 

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:

vzhengdxumsft_0-1728876219159.png

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):

vzhengdxumsft_1-1728876338654.png

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.

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?

poko_0-1728984749497.png

poko_2-1728984908488.png

or this similar  error

poko_0-1728999572079.png

 

I get this error as well

poko_1-1728999729619.png

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.