Forum Discussion

fa5fou5's avatar
fa5fou5
Regular Visitor
7 years ago
Solved

How to create a Custom Column or New Table based with multiple Column condition

Hi,

Suppose I am having such Table,

How Can I Create a custom column called <Actual Milestone> that calculate the actual milestone based on the earliest date with is in this case 16-Apr-19

 

ProjectMilestoneCompleted OnActual Milestone
59910 Milestone 16-Jan-19Milestone 4
59910 Milestone 21-Feb-19Milestone 4
59910 Milestone 314-March-19Milestone 4
59910 Milestone 416-Apr-19Milestone 4
59910 Milestone 5 Milestone 4

 

Or Even Better, 

To create a new Table that shows only 

ProjectCompleted OnActual Milestone
5991016-Apr-19Milestone 4

 

Thanks a lot

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi buddy,

     

    well, this is happening because you have one date being repeted, so you need something like "index column".

    I create one column called "KEY" it would serve to resolve your problem, here goes the steps.

     

    i recreate the table, now only using the summerize for column "Project":

    Table = SUMMARIZE(Table1;Table1[Project])
     
     
     
     
     
    Create those 2 columns:
    last date = CALCULATE(LASTDATE(Table1[Date]);FILTER(Table1;Table1[Project]='Table'[Project]))
    Key = CONCATENATE('Table'[Project];'Table'[last date])
     
     
     
     
     
     
    Create on the original table this column to be referenced:
    Key = CONCATENATE(Table1[Project];Table1[Date])
     
     
     
     
    Those "key" columns you can "hide in the report view" so do impact happens.
     
    Now you can access the Name column, like this:
    Column = LOOKUPVALUE(Table1[Name];Table1[Key];'Table'[Key])
     
     
     
     
     
    This should do the trick,
    Also, sorry my bad english, it's not my native language.
     
    Any questions, ask ;)
     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi buddy,

     

    Try create a table on DAX like i do.

     

    1 - Create a table with this DAX: 

    Table = SUMMARIZE(Table1;Table1[Project];"Actual Milestone";LOOKUPVALUE(Table1[Milestone];Table1[Completed On];LASTDATE(Table1[Completed On])))
     
    This will bring to you 2 columns, "Project" and "Actual Milestone", the second column alreasy is refering to the last date of "Table1[Completed On]", so is dynamic.
     
    2- Create a calculated column with this: 
    Completed On = LASTDATE(Table1[Completed On])
     
     
    this should do the trick.
    Any questions, ask ;)
     
     
     
     
    • fa5fou5's avatar
      fa5fou5
      Regular Visitor
      7 years ago

      Thank you for your reply,

      but I get this error message

      'A table of multiple values was supplied where a single value was expected.'

      I think because I have a column that contain more than on project, In fact the final table would be like

      Project 1  --- Milestone 2  --- Date1

      Project2 --- Milestone 1 --- Date2

      Project3 --- Milestone 3 --- Date3

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Try this next one:

         

        Table = SUMMARIZE(Table1;Table1[Project];"Actual Milestone";LOOKUPVALUE(Table1[Milestone];Table1[Completed On];LASTDATE(Table1[Completed On]));"Date";LOOKUPVALUE(Table1[Completed On];Table1[Completed On];LASTDATE(Table1[Completed On])))
         
        this one is creating a table who can bring you all info in one.
        Be sure to hit the "create table" button on the modeling to use this DAX, just like this.
         
        Just to clarify, i'm using the SUMMARIZE to group the project, the milestone and the last date.