topic Re: Unique value in DAX Commands and Tips

Unique value

ChiRomeu — Mon, 05 Sep 2022 17:46:42 GMT

Dear member,

could you please kindly help me to solve the following problem:

I would like to get this result in table of power.

Best regards

Chi

Re: Unique value

ribisht17 — Mon, 05 Sep 2022 17:55:33 GMT

ChiRomeu

Option 1 - https://forum.enterprisedna.co/t/counting-first-occurences/17688/12

Option 2-https://www.myonlinetraininghub.com/excel-forum/power-query/flag-first-instance-of-a-value-in-one-column-as-it-relates-to-the-running-dates-in-another-column

Regards,

Ritesh

Re: Unique value

Whitewater100 — Mon, 05 Sep 2022 18:01:22 GMT

Hi:

Assuming your table name = "Data" you can use this caclculated column:

Unique =

VAR CountID =

COUNTROWS(

FILTER(ALL(Data),

'Data'[Master_ID] = EARLIER(Data[Master_ID])))

RETURN

IF(CountID >1,0, 1)

I hope this helps!

Re: Unique value

ChiRomeu — Mon, 05 Sep 2022 18:41:13 GMT

Thank you for info.

Is it possible to include the first row about the >2 rows?

best regards

Re: Unique value

Whitewater100 — Mon, 05 Sep 2022 18:46:33 GMT

YEs, like this?

Unique R =

IF(

COUNTROWS(

FILTER(ALL(Data),

'Data'[Master_ID] = EARLIER(Data[Master_ID])))

>1,0, 1)

If this helps, please mark as solution.Have a nice day!

Re: Unique value

tamerj1 — Mon, 05 Sep 2022 19:17:55 GMT

HI ChiRomeu
Usually this requires an index column easily added using power query with one click. However, if this is not possible for any reason you can create a new calculated table as follows. (refer to attached sample file)

Where 'Data' is the original table

Data New = VAR Items = CONCATENATEX ( Data, Data[MasterId], "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Index", [Value], "@MasterID", PATHITEM ( Items, [Value] ) ) VAR T3 = ADDCOLUMNS ( T2, "@UniqueValue", VAR CurrentID = [@MasterID] VAR CurrentIndex = [@Index] VAR FirstIndex = MINX ( FILTER ( T2, [@MasterID] = CurrentID ), [@Index] ) RETURN IF ( CurrentIndex = FirstIndex, 1, 0 ) ) RETURN SELECTCOLUMNS ( T3, "Master ID", [@MasterID], "Unique Value", [@UniqueValue] )

Re: Unique value

ChiRomeu — Tue, 06 Sep 2022 06:43:10 GMT

Thanks a lot!