The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Hi, @Anonymous
try below code
Measure 3 = IF(
ISINSCOPE(Table1[contractor]),
CONCATENATEX (
CALCULATETABLE ( VALUES ( Table1[leasenumber] ) ),
Table1[leasenumber],
", " ,
Table1[leasenumber]
,ASC
),
BLANK()
)
@Anonymous, Try this code in a calculated table,
New_Table =
VAR _summarize = SUMMARIZE('Table','Table'[contractnbr],"leasenumber",MIN('Table'[leasenumber]))
RETURN _summarize
Regards,
Shalini
It says the syntax for 'RETURN' is incorrect.
Also, need ALL the leasenumbers for each contractnbr.
Hi @Anonymous ,
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
No, I need all the distinct leasenumbers.