SELECTCOLUMNS with distinct and Max?

[Anonymous]

Hi all, 

Any help would be greatly Appreciated. I'm struggling for some months now with DAX in general.

I'm trying to create a table of unique id's from a larger table. I'm pulling 2 columns to begin with (would like more ultimatley). First is [Learner No] which I'm apply Distinct to. Second is [Full Name]. There are some clients who have name variations and I want to just use the first version of their name that appears. 

Unique No = Distinct(
             SELECTCOLUMNS(
                    Learners, [Learner No],
                              [Full Name])
                    )

I've tried putting in MAX after the first column. which fails. And I've tried putting the distinct inside the SELECTCOLUMNS, with no luck. I guess I understand that making [Learner No] Distinct, and [Full Name] MAX, need to be separated, but I can't work out how to combine them within SELECTCOLUMNS.

(It needn't be MAX, not sure MAX is right. I just wanted to use the first [Full Name] that appears). 

Many thanks for any help,

DJ 

[HotChilli]

Is this a data-cleaning task? If so, wouldn't it be better to do it in Power Query?

You say you are creating a "table of unique id's from a larger table" but it sounds like you want to reduce a table of Learners to a table with 1-row-per-person .

If you want  a proper answer please post a few rows of fake data that illustrate the issue and what you want the result to be.

[Anonymous]

Hi HotChilli 

Thank you. I have since created it in Power Query. I couldn't initially due to PBI forcing a text column into a number column which then led to a data type mismatch when saving the related table. 

I've been running these two solutions in parallel. And while the power query may be the better approach

Procedurally. (I haven't had a chance to test the data it has output yet). I still assume it should be possible to do as a new table using SELECTCOLUMNS.

I am trying to create a unique key, not a data cleanse. The idea was to have the unique key as a starting point for the rest of the report's data model to link to. Since every table I am working with has multiple lines for each user and no truly unique key. 

So the above 'New table' Dax provides this:

Learner No	Full Name
123456	Davy Jones
123456	Davy Paul Jones
654321	Sarah Conner

and my aim would be to have: (I don't care which version of Davy's name I get).

Learner No	Full Name
123456	Davy Jones
654321	Sarah Conner

Each time I add a column into the table the number of rows balloons according to the number of variations in said column recorded against the full list of [Learner No].

If I just had the [Learner No] Column I'd have a unique code and could create another table to bring in the other data, I guess. But I suspect the same ballooning of rows will occur in the new table if I have no way to limit them?

If I get time this afternoon, I will check the power query table and see what it has actually output. But I'm still interested in the DAX solution. 

Thank you for your time and help 

DJ