Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

 

 

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

     

    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.

  • Anonymous's avatar
    Anonymous
    1 year ago

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

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi Anonymous ,

     

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



    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous 

         

        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.