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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ManuApo
Helper II
Helper II

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

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:

IDCounter
10001
10000
10000
10011
10010
10021
10020
10020
10031
10041


Thank you so much in advance to everyone!!

Best,
Manu.

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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 )

v-henryk-mstf_0-1611909627172.png

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.

View solution in original post

7 REPLIES 7
v-henryk-mstf
Community Support
Community Support

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 )

v-henryk-mstf_0-1611909627172.png

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
Super User
Super User

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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
Helper II
Helper II

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
Super User
Super User

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"

 

manu.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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