Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors