Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Select values from two tables and paste them into new table

So i want to create a new measure called Person Absence Date that would return absence dates for a person from two tables (

'GAM Absence' and 'GAM AbsenceRequest'. 
 
I wanted to do something like this: 
Person Absence Dates =
VAR selectedName = MAX('Issues'[Assignee])
VAR selectedUID = MAXX(FILTER('GAM Users', 'GAM Users'[name] = selectedName), 'GAM Users'[PersonID])
RETURN CONCATENATEX(
     FILTER('GAM Absence',
            'GAM Absence'[UID] = selectedUID ) ,
     'GAM Absence'[Date] & ", ", ,
     'GAM Absence'[Date],
     ASC )
& CONCATENATEX(
     FILTER('GAM AbsenceRequest',
          'GAM AbsenceRequest'[UID] = selectedUID ) ,
     'GAM AbsenceRequest'[Date] & ", ", ,
     'GAM AbsenceRequest'[Date],
     ASC
)
 
This however returns a list of the dates (one row, dates seperated by commas), not dates in rows. How would i do that?
 
 
"MEASURE"
Lists
New Table

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Based on what I got Try like

     

    Person Absence Dates =
    VAR selectedName = MAX('Issues'[Assignee])
    VAR selectedUID = MAXX(FILTER('GAM Users', 'GAM Users'[name] = selectedName), 'GAM Users'[PersonID])
    RETURN CONCATENATEX(
    Distinct( Union( Summarize(FILTER('GAM Absence',
    'GAM Absence'[UID] = selectedUID ) ,'GAM Absence'[Date])
    , Summarize(FILTER('GAM AbsenceRequest',
    'GAM AbsenceRequest'[UID] = selectedUID ) ,'GAM AbsenceRequest'[Date])))
    'GAM Absence'[Date], " , "
    ASC )

     

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.