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
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())
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)
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:
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.
Solved! Go to 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.
Ok this problem was solved if you get this error, check again relationships between tables
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIt 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
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.
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
I get this error as well
Ok this problem was solved if you get this error, check again relationships between tables
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
83 | |
76 | |
64 |
User | Count |
---|---|
142 | |
112 | |
98 | |
98 | |
94 |