Forum Discussion

rhaining's avatar
rhaining
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

want derived table with only distinct values

My source table has dupes, by design.  It's denormalized.  Imagine something like this:   Item ID | Item Name | Item Color | Owning Org | Owning Person ========================================= 1...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    rhaining Maybe:

     

    New Table = 
  SUMMARIZE(
    'Table',
    [ID],
    [Item Name],
    [Item Color],
    "Owning Org", MIN('Table'[Owning Org]),
    "Owning Person", MIN('Table'[Owning Person])
  )


or

New Table = 
  VAR __Table = 
  SUMMARIZE(
    'Table',
    [ID],
    [Item Name],
    [Item Color],
    [Owning Org],
    [Owning Person],
    "Count", COUNTROWS('Table')
  )
  VAR __Max = MAXX(__Table, [Count])
  VAR __Result = TOPN( 1, FILTER(__Table, [Count] = __Max))
RETURN
  __Result

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

rhaining Maybe:

 

New Table = 
  SUMMARIZE(
    'Table',
    [ID],
    [Item Name],
    [Item Color],
    "Owning Org", MIN('Table'[Owning Org]),
    "Owning Person", MIN('Table'[Owning Person])
  )


or

New Table = 
  VAR __Table = 
  SUMMARIZE(
    'Table',
    [ID],
    [Item Name],
    [Item Color],
    [Owning Org],
    [Owning Person],
    "Count", COUNTROWS('Table')
  )
  VAR __Max = MAXX(__Table, [Count])
  VAR __Result = TOPN( 1, FILTER(__Table, [Count] = __Max))
RETURN
  __Result

 

rhaining's avatar
rhaining
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

I haven't yet tried either of your specific suggestions, but they were enough to unblock me.  Thank you!  I decided to start from here:

 

New Table = 
  SUMMARIZE(
    'Table',
    [ID],
    [Item Name],
    [Item Color]
)

 

In effect throwing away all the ownership information.  At least I have a correctly deduped table.

 

I worry about your 1st suggestion -- how does MAX function w.r.t. strings?  And would it guarantee that the Owning Org + Owning Person were a valid pair?  Your 2nd suggestion looks like it would work perfectly and I may try that soon.

 

I have a follow-up question which I'm hoping you or someone else can answer.  I have my main page working fine, and users can filter in many different ways, and all changes they make reflect in all views -- great.  Now I wish my deduped item data set was also filtered to the selections made on report page 1, and would dynamically update as filter changes were applied on that page.