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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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 ;)