## Desktop

Frequent Visitor
Posts: 8
Registered: ‎12-22-2016

# Find the Distinct Value based on other columns

Hi,

I want to create a dax formula that will produce a list of unique guest values from Column A, and find only the distinct value in column B (Host) based on the latest date.  The final report will only have a list of distinct guests and one host per guest.  Although, I would expect to see multiple hosts from the entire list.  The answer below should be as follows:

Server A has a Host A because the last report date is 04/17/2017.

Thank you,

Tim

 Guest Host Report Date server A Host A 4/17/2017 server A Host B 4/10/2017 server A Host C 4/3/2017 server A Host D 3/20/2017 server A Host E 2/20/2017 server A Host F 2/13/2017

Accepted Solutions
Community Support Team
Posts: 3,697
Registered: ‎07-17-2016

## Re: Find the Distinct Value based on other columns

Hi @timstacy1,

It seems that there are some Guests have multiple Hosts with the same last Report Date. For example, Server A has both Host A and Host B with the last report date 04/17/2017 in your table like below.

 Guest Host Report Date server A Host A 4/17/2017 server A Host B 4/17/2017 server A Host C 4/3/2017 server A Host D 3/20/2017 server A Host E 2/20/2017 server A Host F 2/13/2017

So what's your expected result in this scenario, show only Host A, or both Host A and B for Server A in the list?

If you still need a list of distinct guests and one host per guest, the formula below show work in your scenario.

```HostLatestReport =
CALCULATE (
FIRSTNONBLANK ( Table2[Host], 1 ),
FILTER (
ALLEXCEPT ( Table2, Table2[Guest] ),
Table2[Report Date] = [LatestReport]
)
)
```

Regards

Community Support Team
Posts: 3,697
Registered: ‎07-17-2016

## Re: Find the Distinct Value based on other columns

Hi @timstacy1,

Regards

All Replies
Super User
Posts: 1,513
Registered: ‎05-10-2016

## Re: Find the Distinct Value based on other columns

[ Edited ]

@timstacy1

Hi Friend, Check this measure:

```LatestReport =
CALCULATE ( MAX ( Table2[Report Date] ), ALLEXCEPT ( Table2, Table2[Guest] ) )```

```HostLatestReport =
CALCULATE (
VALUES ( Table2[Host] ),
FILTER (
ALLEXCEPT ( Table2, Table2[Guest] ),
Table2[Report Date] = [LatestReport]
)
)```

Lima - Peru
Power BI
Frequent Visitor
Posts: 8
Registered: ‎12-22-2016

## Re: Find the Distinct Value based on other columns

Hello,

Thank you.  The measures generated the following erros:

MdxScript(Model)(10,49) Calculation error in measure 'allguests'[hostlatestreport]:  a table ofmultiple values was supplier where a single value was expected.

I have a long list of guest and host entries.  Could this be the source of the error?

Thank you,

Tim

Community Support Team
Posts: 3,697
Registered: ‎07-17-2016

## Re: Find the Distinct Value based on other columns

Hi @timstacy1,

It seems that there are some Guests have multiple Hosts with the same last Report Date. For example, Server A has both Host A and Host B with the last report date 04/17/2017 in your table like below.

 Guest Host Report Date server A Host A 4/17/2017 server A Host B 4/17/2017 server A Host C 4/3/2017 server A Host D 3/20/2017 server A Host E 2/20/2017 server A Host F 2/13/2017

So what's your expected result in this scenario, show only Host A, or both Host A and B for Server A in the list?

If you still need a list of distinct guests and one host per guest, the formula below show work in your scenario.

```HostLatestReport =
CALCULATE (
FIRSTNONBLANK ( Table2[Host], 1 ),
FILTER (
ALLEXCEPT ( Table2, Table2[Guest] ),
Table2[Report Date] = [LatestReport]
)
)
```

Regards

Frequent Visitor
Posts: 8
Registered: ‎12-22-2016

## Re: Find the Distinct Value based on other columns

You are correct.  I am only interested in a unique host for every unique guest, using the latest date as the determining factor.  My apologies for the flawed table but glad you figured it out.  I applied the measure and it worked!  I can't believe it.  I can't tell you what a help this is.   Now, I need to figure out why it works.  And, due to the size of the file I am working with, I run out of memory.

Thank you again!

Tim

Community Support Team
Posts: 3,697
Registered: ‎07-17-2016

Hi @timstacy1,