topic Re: Convert Calculated Column to DAX Measure to detect one of two values in DAX Commands and Tips

Convert Calculated Column to DAX Measure to detect one of two values

JonKho — Mon, 20 May 2024 07:59:29 GMT

Hi all

Previously from this thread, https://community.fabric.microsoft.com/t5/Desktop/How-to-find-duplicate-values-in-one-column-while-another-column/m-p/3894349

Thanks to one for the members, Ashish_Mathur to create a way to find the duplicate values through the formula shown below.

Column = if(CALCULATE(DISTINCTCOUNT(Scanned_Item_Two_Controllers[Scan_Controller_Node_Id]),FILTER(Scanned_Item_Two_Controllers,Scanned_Item_Two_Controllers[Page_Barcode]=EARLIER(Scanned_Item_Two_Controllers[Page_Barcode])))>1,"Duplicate Found!","Unique")

I have created another new measure that represent what I want to do where I can detect, 'Duplicate Found!'

Filter Value =
CALCULATE(
COUNTA('Scanned_Item_Two_Controllers'[Column]),
'Scanned_Item_Two_Controllers'[Column] IN { "Duplicate Found!" }
)

I hope to get more insights on this matter.

Thank you for reading this thread.

Re: Convert Calculated Column to DAX Measure to detect one of two values

xifeng_L — Mon, 20 May 2024 08:31:48 GMT

Hi JonKho ,

You can try below measure.

Measure = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        CALCULATE(
            DISTINCTCOUNT('Table'[Controller_Node_Id]),
            ALLEXCEPT('Table','Table'[BarCode])
        )>1
    )
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Convert Calculated Column to DAX Measure to detect one of two values

mark_endicott — Mon, 20 May 2024 11:19:49 GMT

JonKho - If you are trying to count the number of Duplicate Values I would use:

CALCULATE ( COUNTROWS ( Table ), KEEPFILTERS ( Table[Column] = "Duplicate Value" ) )

If you would like the number of ID's with duplicates you can use:

CALCULATE ( DISTINCTCOUNT ( Table[Column] ), KEEPFILTERS ( Table[Column] = "Duplicate Value" ) )

Re: Convert Calculated Column to DAX Measure to detect one of two values

JonKho — Tue, 21 May 2024 09:14:28 GMT

Thank you xifeng_L

Your solution works.

If you are available, I appreciate that you can help me to provide your insight on this topic, https://community.fabric.microsoft.com/t5/Desktop/Unable-to-perform-COUNT-for-flag-value/m-p/3934860#M1260943