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
LudivineLOU
Helper I
Helper I

count - liste répétitions

Hello,

 

I would like to count the number of repetitions in function of my columns Purchase Doc. on PoweBI

 

In excel, I use the formula NB.SI

 

Pur. Doc. Itmdateqtynumber
60113.02.202021
60113.02.202022
60126.09.201983
60115.11.201984
60125.10.2019105
60215.11.2019101
60228.11.201972
60210.12.201973
60223.12.2019104
60220.01.2020105
60321.02.202051
60323.03.2020102
60314.04.2020103
60429.05.2020101
60426.06.2020102
60726.09.201931
60722.04.202012
61010.09.201921
61025.10.201952
61215.11.201951
61328.11.201981

 

My goal is to calculate aumatically the columns "Number"

 

Do you have a idea of the function DAX to used ?

 

Thank you very much for your help, Best regards,

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @LudivineLOU ,

To get the same expected result as the table, you need to add an index column manually in power query first:

index column.png

Then create a calulated column like this:

Column =
CALCULATE (
    COUNT ( 'Table'[Pur.Doc.Itm] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Pur.Doc.Itm] ),
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
    )
)

You will get the expected result:

result.png

 

Attached my sample file that hopes to help you, please check and try it: count.pbix

 

Best Regards,
Yingjie Li

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

5 REPLIES 5
LudivineLOU
Helper I
Helper I

THANK YOU very much everbody, it works 🙂 

have a nice day, 

Best regards,

Ashish_Mathur
Super User
Super User

Hi,

Try this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pur. Doc. Itm", Int64.Type}, {"date", type date}, {"qty", Int64.Type}}),
    Partition = Table.Group(#"Changed Type", {"Pur. Doc. Itm"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"date", "qty", "Index"}, {"date", "qty", "Index"})
in
    #"Expanded Partition"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yingjl
Community Support
Community Support

Hi @LudivineLOU ,

To get the same expected result as the table, you need to add an index column manually in power query first:

index column.png

Then create a calulated column like this:

Column =
CALCULATE (
    COUNT ( 'Table'[Pur.Doc.Itm] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Pur.Doc.Itm] ),
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
    )
)

You will get the expected result:

result.png

 

Attached my sample file that hopes to help you, please check and try it: count.pbix

 

Best Regards,
Yingjie Li

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

amitchandak
Super User
Super User

@LudivineLOU , As dates are repeating you might need a index column . or use rank with a tie breaker

countx(filter(Table, table[Pur. Doc.] =earlier(table[Pur. Doc.] ) && table[Itm date] <=earlier(table[Itm date])
&& table[index] <=earlier(table[index]) ),table[Pur. Doc.] )

 

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

 

Greg_Deckler
Super User
Super User

@LudivineLOU - You need Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.