Forum Discussion

timstacy1's avatar
timstacy1
Frequent Visitor
9 years ago
Solved

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

 

 

 

GuestHostReport Date
server AHost A4/17/2017
server AHost B4/10/2017
server AHost C4/3/2017
server AHost D3/20/2017
server AHost E2/20/2017
server AHost F2/13/2017
Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

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

     

    Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi timstacy1,

     

    Glad to help! Could you accept helpful replies as solution to close this thread? :smileyhappy:

     

    Regards

5 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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]
    )
)

     

     

     

     

  • timstacy1's avatar
    timstacy1
    Frequent Visitor
    9 years ago

    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

     

     

    • timstacy1's avatar
      timstacy1
      Frequent Visitor
      9 years ago

      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

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi timstacy1,

         

        Glad to help! Could you accept helpful replies as solution to close this thread? :smileyhappy:

         

        Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. :smileyhappy:

     

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

     

    Regards