cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bhaskar_K1986
Regular Visitor

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

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]
        )
    )
SKUHPPNMPNProduct selectedCommon/Distinct
SKU1HPPN1MPN1TRUECommon
SKU1HPPN1MPN2TRUECommon
SKU1HPPN1MPN3TRUECommon
SKU1HPPN2MPN4TRUECommon
SKU1HPPN2MPN5TRUECommon
SKU1HPPN3MPN6TRUEDistinct
SKU1HPPN3MPN7TRUEDistinct
SKU1HPPN4MPN8TRUEDistinct
SKU1HPPN5MPN9TRUEDistinct
SKU1HPPN6MPN10TRUEDistinct
SKU2HPPN7MPN11TRUEDistinct
SKU2HPPN8MPN12TRUEDistinct
SKU2HPPN2MPN4TRUECommon
SKU2HPPN2MPN5TRUECommon
SKU2HPPN1MPN1TRUECommon

 

Bhaskar_K1986_0-1664363911430.png

 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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

vcgaomsft_0-1664418983038.png

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

View solution in original post

1 REPLY 1
v-cgao-msft
Community Support
Community Support

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

vcgaomsft_0-1664418983038.png

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors