skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Distinct Column Combinations

    Distinct Column Combinations

    04-07-2018 13:34 PM - last edited 06-27-2018 10:32 AM

    Super User Greg_Deckler
    Super User
    3560 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Distinct Column Combinations

    ‎04-07-2018 01:34 PM

    Suppose you have some data like this:

     

    ID            DimKey3            DimKey4          DimKey5          DimKey6

    1 blue red green green
    2 blue blue red blue
    3 blue red blue blue
    4 green green red green
    5 green green blue green
    6 red red red red
    7 blue blue blue blue
    8 green green green green
    9 blue green red green
    10 blue red green green

     

    You wish to find how many unique combinations that you have for just DimKey3, DimKey4 and DimKey5. DimKey6 does not matter.

     

    To get the number of distinct rows where order matters:

     

    mDistinctCombos = 
    VAR tmptable1 = SELECTCOLUMNS(DistinctCombos,"key3",[DimKey3],"key4",[DimKey4],"key5",[DimKey5])
    VAR tmptable2 = DISTINCT(tmptable1)
    VAR distcount = COUNTROWS(tmptable2)
    RETURN distcount

     

     

    To get the number of distinct rows where order does not matter:

     

    mDistinctCombosUnique = 
    // This measure gets unique keys where order doesn't matter. In other words, red, green, blue is the same as blue, red, green
    // Start by getting only the columns we care about
    VAR tmptable1 = SELECTCOLUMNS(DistinctCombos,"key3",[DimKey3],"key4",[DimKey4],"key5",[DimKey5])
    // Add columns that return the MAX and MIN of all 3 columns for each row
    VAR tmptable1a = ADDCOLUMNS(tmptable1,"max",MAX(MAX([key3],[key4]),[key5]),"min",MIN(MIN([key3],[key4]),[key5]))
    // Create a single string with all 3 column values and remove the max and min values calculated above, we now have our 3rd value
    VAR tmptable1b = ADDCOLUMNS(tmptable1a,"mid",SUBSTITUTE(SUBSTITUTE([key3] & [key4] & [key5],[max],"",1),[min],"",1))
    // We have now effectively "sorted" the values in the three columns for each row, so select our new 3 "sorted" columns
    VAR tmptable2 = SELECTCOLUMNS(tmptable1b,"max",[max],"min",[min],"mid",[mid])
    // Now get the distinct rows in the table
    VAR tmptable3 = DISTINCT(tmptable2)
    // Count the number of rows
    VAR distcount = COUNTROWS(tmptable3)
    RETURN distcount

     

     

     

     

     

    eyJrIjoiNzdmMDAwYTgtMGE3MS00ZTk4LTg1ZTctN2M0Y2RlMmZjNTc3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    DistinctCombos.pbix
    13 KB
    Labels:
    • Labels:
    • Other
    • Totals
    Message 1 of 2
    3,560 Views
    0
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    EnglishCJ
    EnglishCJ
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-22-2020 10:36 PM

    For more than 3 values, would you suggest a strategy of concatination, PATH, and PATHLENGTH, instead of MIN and MAX?

    Message 2 of 2
    2,154 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices