Struggling! How to create a column with 1 and 0 that counts distinct ID?

[ManuApo]

Good afternoon everyone, 

Im struggling trying to create a column that basically returns a 1 for the first instance of another column, in this case, an ID, to the other instances should return a 0.  I am trying to get this with a column not a DAX. 

I would really appreciate if someone can help me here! 

Here is an example of my dataset and the ideal column:

ID	Counter
1000	1
1000	0
1000	0
1001	1
1001	0
1002	1
1002	0
1002	0
1003	1
1004	1

Thank you so much in advance to everyone!!

Best,
Manu.

[v-henryk-mstf · Accepted solution]

Hi @ManuApo ,

According to your requirements, I created a Calculated column, and did the following test as a reference:

Column = 
VAR _minindex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    IF ( [Index] = _minindex, 1, 0 )

The official documentation about EARLIER functions can be used as a reference.

If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @ManuApo 

No worries 🙂

The code I provided goes into a query in the Power Query Editor. You can open the editor and then copy/paste my code into a blank query but that will only work for the samepl code I created in the sample PBI file I created.

I linked to a sample PBIX file that you can download and see how it works. Here's the link again

https://d13ot9o61jdzpp.cloudfront.net/files/pbiforum/ManuApo.pbix

If your real world data is different to the sample you posted then the code may need some adjusting.  If this si the case then if you can post your real data I can write the code.

Regards

Phil

[ManuApo]

Hi again @PhilipTreacy , sorry for confusing you.

I would like to create it as a Calculated column (DAX) but not as a measure (I thought DAX and measures were the same). Any idea about that?

I am sure it should be easier than creating a new query and then modifing the code you provided me, to make it work in my real world data.

Really appreciate your help! @PhilipTreacy 

Best,
Manu.

[PhilipTreacy]

Hi @ManuApo 

DAX can be used to create Calculated Columns or Measures.

The code I wrote is in the M langauge used in Power Query, which is also part of Power BI.

Have you tried opening the sample file I provided to see how it works?

Regards

Phil

[ManuApo]

Hi @PhilipTreacy 

Yes I did, but the code in M language used in Power Query is something new for me that I have never seen before. 

That is why I believe it should be much easier to create a calculated column with DAX in order to  solve my problem. 

Im watching some tutorials to better understand about M Code. 

In the other hand, I was wondering if you could help me to create a calculated column in DAX for my problem, instead of using M code and trying to adapt it to my real world data. This is something I will try to do at some point in the future. But I still don't get how useful is to create a new query in order to approach my problem, when I already have a dataset, I just need to create a calculated column that gives me the result I am looking for.  From my humble opinion.

Thank you again and again for your time!! 

[ManuApo]

Hi @PhilipTreacy 
Thanks for your quick response!! I am sorry to ask you this (I am new in Power BI), where should I place that code exactly? In a column? 

Thanks again
Manu

[PhilipTreacy]

HI @ManuApo 

When you say you want this with a column I assume you mean in Power Query as Calculated Columns are created with DAX.

Download this sample PBIX file with data and code.

This code in Power Query does what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFCK1cHBMMRgGOFmGMMYJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then 1 else if Source[ID]{[Index]} = Source[ID]{[Index]-1} then 0 else 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Regards

Phil