Distinct Value with Group By on multiple Columns

Question

I would like to create two columns "Total Distributor" and "Source" based on the below data:

Here's the logic and I need some help transforming this to a calculated column or measure.

"Total Distributor" = Distinct count of "Distributor" that have "Status" = "Active" and grouped by "Item".

"Souce" = If "Total Distributor" > 1 then "Multiple", else "Single"

Item	Distributor	Status	Total Distributor	Source
Mango	Royal Fruits	Active	3	Multiple
Mango	Farmers Market	Active	3	Multiple
Mango	Costco	Active	3	Multiple
Apple	Costco	Active	1	Single
Kiwi	Kroger	Active	2	Multiple
Kiwi	Sams Club	Active	2	Multiple
Grapes	Amazon	Active	1	Single
Papaya	Walmart	Active	1	Single
Papaya	Costco	Inactive	1	Single

Could you please help?

ryan_mayu · Answer

SalHack,&nbsp;You can create two colums.&nbsp;Total Distributor = COUNTX(FILTER('Sheet7',Sheet7[Item]=EARLIER(Sheet7[Item])&amp;&amp;Sheet7[Status]="Active"),Sheet7[Item])source = if(Sheet7[Total Distributor]&gt;1, "multiple","single")&nbsp;

amitchandak · Answer

SalHack , Try a new column like
new Columns =
calculate(distinctcount(Table[Distributor]), filter(Table, table[Item] = earlier(table[Item]) &amp;&amp; Table[Status] ="Active"))
&nbsp;
new Columns =
if(calculate(distinctcount(Table[Distributor]), filter(Table, table[Item] = earlier(table[Item]) &amp;&amp; Table[Status] ="Active")) &gt;1,"Multiple","Single")

salhack · Answer

Thank you. The formula you shared above works fine when I do not have any filters applied on the data. However, I have several filters applied and I need to reflect the calculation based on what is selected and is visible on the screen. Could you advise how to do that?

v-eachen-msft · Answer

Hi SalHack&nbsp;，
&nbsp;
You need to create measures to get dynamic values.
To be independent with original table, you need to create a slicer table firstly( no relationship ).
Slicer =
SELECTCOLUMNS (
    'Table',
    "Item", 'Table'[Item],
    "Distributor", 'Table'[Distributor]
)
Then refer to the following measures to get "Total Distributor" and "Source".&nbsp;
Total Distributor =
VAR a1 =
    FILTER (
        'Table',
        'Table'[Item] = SELECTEDVALUE ( 'Slicer'[Item] )
            &amp;&amp; 'Table'[Distributor] = SELECTEDVALUE ( 'Slicer'[Distributor] )
    )
VAR a2 =
    FILTER ( 'Table', 'Table'[Item] = SELECTEDVALUE ( 'Slicer'[Item] ) )
VAR a3 =
    FILTER (
        'Table',
        'Table'[Distributor] = SELECTEDVALUE ( 'Slicer'[Distributor] )
    )
VAR b =
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[Item] ), 'Table'[Status] = "Active" )
RETURN
    IF (
        ISFILTERED ( 'Slicer'[Distributor] ) &amp;&amp; ISFILTERED ( 'Slicer'[Item] ),
        CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), a1 ), b ),
        IF (
            ISFILTERED ( 'Slicer'[Distributor] )
                &amp;&amp; ( ISFILTERED ( 'Slicer'[Item] ) = FALSE () ),
            CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), a3 ), b ),
            IF (
                ISFILTERED ( 'Slicer'[Item] )
                    &amp;&amp; ( ISFILTERED ( 'Slicer'[Distributor] ) = FALSE () ),
                CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), b ), a2 ),
                CALCULATE ( COUNTA ( 'Table'[Item] ), b )
            )
        )
    )Souce =
IF (
    [Total Distributor] &gt; 1,
    "Multiple",
    IF ( [Total Distributor] = 1, "Single" )
)
&nbsp;Here is my test file for your reference.
&nbsp;

salhack · Answer

Thank you Eads. Couple of things:&nbsp;1. Item is not part of the slicer2. I have other colums as slicer that are not listed in the sample data below.&nbsp;Based on the above changes, would the below script work?&nbsp;

Forum Discussion

Distinct Value with Group By on multiple Columns

9 Replies

Related content

distinct count of multiple columns

Getting distinct values for multiple columns

Count Distinct Across Multiple Columns in PowerBI

Count distinct rows based on multiple columns

Distinctive Count across two columns