Create column to check rows are common or distinct based on another column

[Bhaskar_K1986]

My requirement is that I have a table where I need to check whether the column values are duplicated or distinct based on another column selection with more than 1 value selected.

Steps:

1. From below table First Concatenate HPPN & MPN columns .

2. Now Countrows based on HPPN_MPN concatenated column

ex:  check HPPN1_MPN1 is reflecting in any other SKU from SKU1.

      In SKU2 also HPPN1_MPN1 is available so this is common in both SKU1 and SKU2.

3. Till here i was able to do by using countrows.

Created calculated column based on HPPN_MPN combination and identified the common

and distinct rows.

But now my problem is that i have to countrows based on slicer selection column(SKU)

If i have selected SKU1 then it has to show as distinct.

If i selected SKU1 and SKU2 then it has to show as common.

SKU_Common_Distinct =

    COUNTROWS(

        FILTER(

            All(BOM_BY_SKU),EARLIER(BOM_BY_SKU[HPPN_MPN_Concat])=BOM_BY_SKU[HPPN_MPN_Concat]

             && EARLIER(BOM_BY_SKU[SKU_SA])<>BOM_BY_SKU[SKU_SA]

        )

    )

SKU	HPPN	MPN	Product selected	Common/Distinct
SKU1	HPPN1	MPN1	TRUE	Common
SKU1	HPPN1	MPN2	TRUE	Common
SKU1	HPPN1	MPN3	TRUE	Common
SKU1	HPPN2	MPN4	TRUE	Common
SKU1	HPPN2	MPN5	TRUE	Common
SKU1	HPPN3	MPN6	TRUE	Distinct
SKU1	HPPN3	MPN7	TRUE	Distinct
SKU1	HPPN4	MPN8	TRUE	Distinct
SKU1	HPPN5	MPN9	TRUE	Distinct
SKU1	HPPN6	MPN10	TRUE	Distinct
SKU2	HPPN7	MPN11	TRUE	Distinct
SKU2	HPPN8	MPN12	TRUE	Distinct
SKU2	HPPN2	MPN4	TRUE	Common
SKU2	HPPN2	MPN5	TRUE	Common
SKU2	HPPN1	MPN1	TRUE	Common

[v-cgao-msft · Accepted solution]

Hi @Bhaskar_K1986 ,

Since the calculated column is recomputed when the memory is refreshed, it will not be interacting with the slicer when it finishes its calculation. So if you want to get dynamic results, you may consider using a measure to handle this question.

Please new a measure:

Common/Distinct = 
VAR _HPPN =
    MAX ( 'Table'[HPPN] )
VAR _MPN =
    MAX ( 'Table'[MPN] )
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[HPPN] = _HPPN && 'Table'[MPN] = _MPN )
    )
VAR _result =
    IF ( _count > 1, "Common", "Distinct" )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data