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   P...
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 ;)
     
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.
       
       
      • fa5fou5's avatar
        fa5fou5
        Regular Visitor
        7 years ago

        Thank you,

        In fact,

        when I Have this table of more than 3000 rows, this is a specimen,

        59910CMT1-May-19
        59910TSVR116-May-19
        59910TSVR230-May-19
        59910CB6-Jun-19
        59910HIP10-May-19
        59874CMT10-May-19
        59874TSVR17-May-19
        59874CSRB10-May-19
        59874TSVR221-May-19
        59874CB28-May-19
        59874HIP5-Jun-19

        With this code :

        Table = SUMMARIZE(Table2,Table2[PCR],"Actual Milestone",LOOKUPVALUE(Table2[HIP],Table2[DATE],LASTDATE(Table2[DATE])),"Date",LOOKUPVALUE(Table2[DATE],Table2[DATE],LASTDATE(Table2[DATE])))

        I am getting this result :

        Actual Milestone      Date                          PCR

        HIP                                    6/5/2019               59874

        CB                                      6/6/2019               59910

         

        The Problem as soon as I add rows  (Check I added another row in the end)

        59910CMT1-May-19
        59910TSVR116-May-19
        59910TSVR230-May-19
        59910CB6-Jun-19
        59910HIP10-May-19
        59874CMT10-May-19
        59874TSVR17-May-19
        59874CSRB10-May-19
        59874TSVR221-May-19
        59874CB28-May-19
        59874HIP5-Jun-19
        54786TSVR5-Jun-19

         

        I receive this message:

        A table of multiple vales was supplied where a single value was expected :(