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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ybyb23
Frequent Visitor

Generating dynamic rows and counting them

Hi all, 

 

I have been struggling with my dax function to generate for each row a value 1 then calculte the sum, however whenever I add another field in my table the total should increase but it stays the same. 

 

Is there a dynamic way to simply generate this 1 for each new row with the sum of all of them?

 

Here is what I am using, there are some cells blank as well. 

 

Product Idproduct NameMarketpriceCout
Tv-123Tvstore11
Tv-325Tvonline21
PS4-456PS4  1
PS4-247ps4 21
Total  54

Whenever I add other columns to this table as here below, I keep on seeing the 1 but the total stays the same 

 

Product Idproduct NameMarketsizePricecount
Tv-123tvonline100.51
Tv-123tvonline50.51
Tv-123tvonline811
Tv-123tvonline611

 

Can anyone please help me on that

 

thanks! 

1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
Community Support

Hi @ybyb23 ,

 

There are two reasons which may cause numeric fields not automatically summed:

1, The field data type is "this article" type.

2, There is a modeling relationship.

According to your description, I created a sample, and here is my solution.

Create a column.

 

count =
IF (
    COUNTROWS (
        FILTER (
            'Sheet1',
            'Sheet1'[Product ID] = EARLIER ( 'Sheet1'[Product ID] )
                && 'Sheet1'[Product Name] = EARLIER ( 'Sheet1'[Product Name] )
                && 'Sheet1'[Market] = EARLIER ( 'Sheet1'[Market] )
                && 'Sheet1'[price] = EARLIER ( 'Sheet1'[price] )
                && 'Sheet1'[size] = EARLIER ( Sheet1[size] )
        )
    ) > 0,
    "1",
    BLANK ()
)

 

vxiaosunmsft_0-1664186132732.png

When you add rows to the table, you will get the final output.

vxiaosunmsft_1-1664186132739.pngvxiaosunmsft_2-1664186132740.png

 

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ xiaosun

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

4 REPLIES 4
v-xiaosun-msft
Community Support
Community Support

Hi @ybyb23 ,

 

There are two reasons which may cause numeric fields not automatically summed:

1, The field data type is "this article" type.

2, There is a modeling relationship.

According to your description, I created a sample, and here is my solution.

Create a column.

 

count =
IF (
    COUNTROWS (
        FILTER (
            'Sheet1',
            'Sheet1'[Product ID] = EARLIER ( 'Sheet1'[Product ID] )
                && 'Sheet1'[Product Name] = EARLIER ( 'Sheet1'[Product Name] )
                && 'Sheet1'[Market] = EARLIER ( 'Sheet1'[Market] )
                && 'Sheet1'[price] = EARLIER ( 'Sheet1'[price] )
                && 'Sheet1'[size] = EARLIER ( Sheet1[size] )
        )
    ) > 0,
    "1",
    BLANK ()
)

 

vxiaosunmsft_0-1664186132732.png

When you add rows to the table, you will get the final output.

vxiaosunmsft_1-1664186132739.pngvxiaosunmsft_2-1664186132740.png

 

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ xiaosun

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

Hi @v-xiaosun-msft

 

thank you very much for your support. It worked perfectly! 

 

Also I have approched it differently with the summerize function  I have added all the fields name to count and it worked. 

 

lbendlin
Super User
Super User

Souunds like you want to inspect your data model.  Same value in all rows usually points to a mis-picked column from the wrong side of the relationship.

Hi @lbendlin

what I was trying to do is to see if the number of rows are correct to calculte a % based on another condition 🙂 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.