How do I DAX a new table from the following:

[jileigh]

Given the below, I would like the result to be two columns, with distinct contractnbrs in 1 column and the coresponding leasenumbers in another.  I only want one of each leasenumber, and no blanks.  I have tried groupby, but obviously don't know how to use it as it complains about the duplicate values in contractnbr.

Thanks in advance for your help.

[Dangar332]

Hi, @jileigh 

try below code

Measure 3 = IF(
              ISINSCOPE(Table1[contractor]), 
               CONCATENATEX (
                  CALCULATETABLE ( VALUES ( Table1[leasenumber] ) ),
                  Table1[leasenumber],
                   ", " ,         
                  Table1[leasenumber] 
                 ,ASC              
                ),
                BLANK()
            )

[Shalini_T]

@jileigh, Try this code in a calculated table,

New_Table = 
VAR _summarize = SUMMARIZE('Table','Table'[contractnbr],"leasenumber",MIN('Table'[leasenumber]))
RETURN _summarize

Regards,
Shalini

[jileigh]

It says the syntax for 'RETURN' is incorrect.

[jileigh]

Also, need ALL the leasenumbers for each contractnbr.

[tackytechtom]

Hi @jileigh ,

How about this:

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[contractbr],
   "MaxLeaseNumber", MAX ( 'Table'[leasenumber] )
)

You might need to change the aggregate function to your needs (max, min etc.)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[jileigh]

No, I need all the distinct leasenumbers.