Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Latest Date

EDIT -  Latest Date by 2 ID columns. Is that possible? I tried to concatenate the columns, but got an error "end of the input was reached" Please look for examples in message below. Ta! H...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous

Table 2 is just a connection table.

All it conatins in IDs from table 1 and the related IDs from table 3.

So I dont think I need to get the IDs from the table(s), as they are already there.

 

I am still not able to filter and get the latest date record.

 

Cheers!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

If you can please share a simple sample with same data structure so that I can test on it.

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I believe that Power BI is not able to handle to many relationships.

    There is a need to create tables with lookups and derive data from other tables.

     

    Also, Power BI is limited with the queries inserted when loading the data from the sources. Making it harder to manage the entire model and reports. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hey Anonymous,
    Thanks for coming back.

     

    Here is the data example in below table.

    I have chnaged the data model, as the previous one had many issues.

    Now I have 1 table with all relevant data in it.

    I want to retrieve the latest (publish date) record for each person and category.

    For example:
    Name - ERPo
    I want to show 2 records out of the 4 in the example.

    ^Ce^ will have 3 records, but showing only one with latest date.

    ^Ch^ will have only 1 records, so showing that single one.

     

    ERPo18/lan^Ce^9/01/2019 
    ERPoOOH-16^Ch^16/06/20161/03/2019

     

     

     

    ERPo13/lan^Ce^13/03/2016 
    ERPo18/lan^Ce^9/01/2019 
    ERPoMy 017^Ce^10/01/2017 
    ERPoOOH-16^Ch^16/06/20161/03/2019

     

     FULL Dummy data:

     

    NameDoc NameCategoryPublish DateReview Date
    RTKy201yia^Ce^2/10/20182/10/2018
    RTKy201yia^Ct^21/01/201921/01/2020
    SYKy201pdf^Ch^22/01/2019 
    SYKy201pdf^Ch^22/01/2019 
    SYKy201pdf^Cl^13/12/2018 
    SYKy201pdf^Ch^22/01/2019 
    SYKy201pdf^Ct^13/12/20183/04/2019
    SYKy201ocx^CP^13/12/201812/04/2019
    SYKy201pdf^Ch^13/12/201821/05/2019
    SYKy201pdf^Ce^13/12/201819/09/2019
    SYKy201ocx^Cs^13/12/2018 
    SYKy201ocx^Cs^22/01/2019 
    ASLa201doc^Ct^12/12/2018 
    ONLa201sha^Cl^11/07/2018 
    ONLa201pdf^CP^11/07/2018 
    ONLa201sha^Ce^11/07/201819/04/2019
    ONLa201sha^Ct^11/07/201830/04/2019
    ONLa201sha^Ch^11/09/20187/05/2019
    ONLa201sha^Ch^20/11/2018 
    WELa201ren^Ch^20/11/2017 
    WELa201pdf^Ce^23/01/201823/01/2018
    WELa201pdf^CP^7/03/20187/03/2019
    WELa201doc^Ct^21/03/201821/03/2019
    WELa201ocx^Cl^5/09/20185/09/2019
    ERLiFaC016^Ce^13/07/2016 
    ERLiFaC017^Ce^18/05/2017 
    ERLiFaC018^Ce^18/01/2018 
    ERLo201ocx^Cl^31/01/20196/10/2018
    ERLo201pdf^Ch^31/01/201923/01/2019
    ERLo201pdf^Ce^31/01/201914/05/2019
    ERLo201ocx^CP^31/01/201930/10/2019
    ERLo201doc^Ct^31/01/201916/01/2020
    GHMa201pdf^Ce^8/06/20188/06/2019
    GEMa201ari^Cl^11/07/2018 
    GEMa201ari^Ch^4/10/201815/02/2019
    GEMa201ari^Cl^21/09/201821/02/2019
    GEMa201ari^Ce^11/07/201823/04/2019
    GEMa201ari^Ct^11/07/201824/04/2018
    GEMa201pdf^Cl^14/12/201816/10/2019
    GEMa201ari^Cl^25/01/201916/10/2019
    GSMe201pdf^Cl^10/12/2018 
    GSMe201pdf^Ce^10/12/2018 
    GSMe201pdf^CP^10/12/2018 
    GSMe201doc^Cl^10/12/2018 
    GSMe201pdf^Ce^20/08/2018 
    GSMe201doc^Ct^10/12/2018 
    TEMi201pdf^Ce^3/09/2018 
    TEMi201pdf^Ct^28/09/201828/03/2019
    GENa201ley^Cl^11/07/2018 
    GENa201ley^En^11/07/2018 
    GENa201ley^Ch^4/10/201815/02/2019
    GENa201ily^CP^31/10/2018 
    GENa201ley^Ce^11/07/201823/04/2019
    GENa201ley^Ct^11/07/201830/04/2019
    GENa201ley^Cl^14/12/201816/10/2019
    GENa201ley^Cl^25/01/201916/10/2019
    THNi201ocx^Cl^30/11/2017 
    THNi201doc^Ct^21/01/2019 
    THNi201doc^Ce^12/11/201812/11/2019
    ttNi201non^Cl^12/07/2018 
    ttNi201non^CS^12/07/201824/10/2018
    ttNi201non^Ch^5/12/201812/02/2019
    ttNi201non^Ce^12/07/201821/02/2019
    ttNi201non^CP^1/11/2018 
    ttNi201non^Ct^12/07/201830/04/2019
    ttNi201non^CP^12/07/201814/08/2018
    ttNi201non^CP^5/09/201816/11/2018
    ttNi201non^CP^5/12/201816/02/2019
    ttNi201non^CS^19/12/201811/12/2019
    NSOm201ion^Ct^11/07/201811/01/2019
    NSOm201ion^Ce^11/07/201819/04/2019
    NSOm201ion^CP^11/07/20188/08/2018
    NSOm201ion^CS^12/07/201818/06/2019
    NSOm201ion^CP^5/09/201816/11/2018
    NSOm201ion^CP^4/12/2018 
    ENPh201pdf^CP^29/08/2018 
    ENPh201ocx^Cl^12/12/2018 
    ENPh201pdf^Ce^24/08/201824/08/2019
    ENPh201pdf^Ct^29/08/2018 
    ERPo13/lan^Ce^13/03/2016 
    ERPo18/lan^Ce^9/01/2019 
    ERPoMy 017^Ce^10/01/2017 
    ERPoOOH-16^Ch^16/06/20161/03/2019
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Tried using this solution - https://community.powerbi.com/t5/Desktop/Get-Max-Date-of-2-Columns/td-p/459071

      Giving me an error

       

      A single value for column 'Date' in table 'Table1' cannot be determined.
      This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

       

      Measure =
CALCULATE (
    MAX ( 'Table1'[Call Date] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StoreNameEN] = MAX ( 'Table1'[StoreNameEN] )
            && 'Table1'[UPCUnit] = MAX ( 'Table1'[UPCUnit] )
    )
))
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous,

         

        You can try to use following measure, it will get the last date based on filtered table records and grouped by current category and name fields.

        Measure =
CALCULATE (
    MAX ( T3[Publish Date] ),
    ALLSELECTED ( T3 ),
    VALUES ( T3[Name] ),
    VALUES ( T3[Category] )
)

         

        Regards,

        Xiaoxin Sheng