Distinct Value with Group By on multiple Columns

[SalHack]

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?

[v-eachen-msft]

Hi @SalHack ,

After my tests, I couldn't find a easy way to solve it.

If you have eight slicers, you need to create a slicer table with these columns. Then use ISFILTERED() to judge if it is selected. If true, return the selected value. If false, don't add it to filter.

According to my previous DAX, you need to nest eight IF() statements. This requires repeated work, look forward better solutions from other users.

[SalHack]

I agree. This may work but it's too complex. Would anyone know if there's an easy way to address this?

[amitchandak]

@SalHack , Try a new column like

new Columns =

calculate(distinctcount(Table[Distributor]), filter(Table, table[Item] = earlier(table[Item]) && Table[Status] ="Active"))

new Columns =

if(calculate(distinctcount(Table[Distributor]), filter(Table, table[Item] = earlier(table[Item]) && Table[Status] ="Active")) >1,"Multiple","Single")

[SalHack]

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]

Hi @SalHack ，

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". 

Total Distributor =
VAR a1 =
    FILTER (
        'Table',
        'Table'[Item] = SELECTEDVALUE ( 'Slicer'[Item] )
            && '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] ) && ISFILTERED ( 'Slicer'[Item] ),
        CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), a1 ), b ),
        IF (
            ISFILTERED ( 'Slicer'[Distributor] )
                && ( ISFILTERED ( 'Slicer'[Item] ) = FALSE () ),
            CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), a3 ), b ),
            IF (
                ISFILTERED ( 'Slicer'[Item] )
                    && ( ISFILTERED ( 'Slicer'[Distributor] ) = FALSE () ),
                CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), b ), a2 ),
                CALCULATE ( COUNTA ( 'Table'[Item] ), b )
            )
        )
    )

Souce =
IF (
    [Total Distributor] > 1,
    "Multiple",
    IF ( [Total Distributor] = 1, "Single" )
)

 Here is my test file for your reference.

[SalHack]

Thank you Eads. Couple of things:

1. Item is not part of the slicer

2. I have other colums as slicer that are not listed in the sample data below.

Based on the above changes, would the below script work? 

[SalHack]

so I have the following slicers: 

Item1

Item2

Distributor

Item3

Item4

Item5

Item6

Item7

Item is part of the table view which has multiple columns. 

Could you please help 

[SalHack]

is there a better solution to this requirement?

[ryan_mayu]

@SalHack,

You can create two colums.

Total Distributor = COUNTX(FILTER('Sheet7',Sheet7[Item]=EARLIER(Sheet7[Item])&&Sheet7[Status]="Active"),Sheet7[Item])

source = if(Sheet7[Total Distributor]>1, "multiple","single")