Forum Discussion

kakashi-jk3's avatar
kakashi-jk3
Frequent Visitor
9 years ago
Solved

Need help with filtering latest date by projectcode

How can I filter latestdate by projectCode?   Here is my table:   I want to filter latest date, so it only show the latest report grouped by each project code. But I want to display all the...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi kakashi-jk3,

     

    You could create a new calculated table which has been filtered based on some syntax using formula like below: (Suppose source table is named as 'projecttable')

    Filter latest date rows =
CALCULATETABLE (
    projecttable,
    FILTER (
        projecttable,
        projecttable[CreatedDate]
            = CALCULATE (
                MAX ( projecttable[CreatedDate] ),
                ALLEXCEPT ( projecttable, projecttable[ProjectCode] )
            )
    )
)

    Above DAX will return all the columns from the table, including [status] and [id]. If you only want to return some specific columns in result table, you could use SELECTCOLUMNS to specify those columns you need.

    Filter latest date rows =
SELECTCOLUMNS (
    CALCULATETABLE (
        projecttable,
        FILTER (
            projecttable,
            projecttable[CreatedDate]
                = CALCULATE (
                    MAX ( projecttable[CreatedDate] ),
                    ALLEXCEPT ( projecttable, projecttable[ProjectCode] )
                )
        )
    ),
    "projectcode", [ProjectCode],
    "CreateDate", [CreatedDate],
    "etcHours", [etcHours]
)

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi kakashi-jk3,

 

You could create a new calculated table which has been filtered based on some syntax using formula like below: (Suppose source table is named as 'projecttable')

Filter latest date rows =
CALCULATETABLE (
    projecttable,
    FILTER (
        projecttable,
        projecttable[CreatedDate]
            = CALCULATE (
                MAX ( projecttable[CreatedDate] ),
                ALLEXCEPT ( projecttable, projecttable[ProjectCode] )
            )
    )
)

Above DAX will return all the columns from the table, including [status] and [id]. If you only want to return some specific columns in result table, you could use SELECTCOLUMNS to specify those columns you need.

Filter latest date rows =
SELECTCOLUMNS (
    CALCULATETABLE (
        projecttable,
        FILTER (
            projecttable,
            projecttable[CreatedDate]
                = CALCULATE (
                    MAX ( projecttable[CreatedDate] ),
                    ALLEXCEPT ( projecttable, projecttable[ProjectCode] )
                )
        )
    ),
    "projectcode", [ProjectCode],
    "CreateDate", [CreatedDate],
    "etcHours", [etcHours]
)

Best regards,
Yuliana Gu

  • kakashi-jk3's avatar
    kakashi-jk3
    Frequent Visitor
    9 years ago

    Thanks alot v-yulgu-msft, I managed to make it work with my own code. 

    I realized that the problem my code didn't work is because I didnt use import mode, but rather direct query.

     

    The next filtering I need to do is like:

     

    For example, I got columns Margin , adjusted margin, EAC timer and adjusted timebudget.

    Now I want to filter and say: Display the reports for the projectcode if reports has "Margin is less than adjusted margin".

    Display reports where EAC is X% larger than adjusted timebudget.

     

    How can I do this? Do you know if regular SQL query works in the DAX?

     

    From the answer you gave me, now it will output all the latest reports.

    But I'm also using a slicer tool, which gives me the oppurtunity to choose which projects I want to display the reports for.

    I have set by default that if no project is selected in the slicer, we should display all reports for all the projects. But if I'm choosing a project in the slider, I only want to display the latest report as mentioned, is it a way to do this?

     

    Another problem now is with the live connection from direct query, cus I needed to change to import to get to create a new table, but now I just realized in powerbi.microsoft.com, whenever I update the database from Azure SQL it doesnt update there as it used to be.

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      9 years ago

      Hi kakashi-jk3,

       

      Would you please post your new questions as a new forum thread so that more community members can view it and you would get response more quickly? Also, please provide sample data with your post so that it can help others better understand your requirement and reproduce same scenario in their environment.

       

      Thanks,
      Yuliana Gu