cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Yqy
Helper I
Helper I

Count the Variable when it appear the first time

Hi, i have a table with many variables and it have duplicates, what i want to do is to have count for the variable when it appear the first time and does not count the variable when is appear the second time onwards.

Var VarCount
A A1
A Anull
B B1
B Bnull
C C1
D D1
1 ACCEPTED SOLUTION

Hi again, 
Yes, it is possible please follow these few steps (after adding index column):
1. Group by with counting all rows:

Ritaf1983_0-1685934745914.png

2. Expand only index

Ritaf1983_1-1685934826919.png

3. Add a conditional column

Ritaf1983_2-1685934904129.png

4. remove unnecessary columns
RESULT :

Ritaf1983_3-1685934966299.png

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




View solution in original post

5 REPLIES 5
Ritaf1983
Super User
Super User

Hi @Yqy 
You can achieve it with 2steps :
1 . In PQ add the index column:

Ritaf1983_0-1685932313938.png

2. In the Data editor add a column with this Dax:

first occurrence check =
IF (
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table','Table'[Var]),
            'Table'[Index] <= EARLIER (  'Table'[Index] )
        )
    )
        > 1,
    blank(),
    1
)

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



Hi, is it possible to do the second step in Query using M language instead of in the data editor using DAX

Hi again, 
Yes, it is possible please follow these few steps (after adding index column):
1. Group by with counting all rows:

Ritaf1983_0-1685934745914.png

2. Expand only index

Ritaf1983_1-1685934826919.png

3. Add a conditional column

Ritaf1983_2-1685934904129.png

4. remove unnecessary columns
RESULT :

Ritaf1983_3-1685934966299.png

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




Thanks for the help, your solution works.

I was happy to help 😊

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors