topic Re: Generating dynamic rows and counting them in DAX Commands and Tips

Generating dynamic rows and counting them

ybyb23 — Fri, 23 Sep 2022 20:13:22 GMT

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 Id	product Name	Market	price	Cout
Tv-123	Tv	store	1	1
Tv-325	Tv	online	2	1
PS4-456	PS4			1
PS4-247	ps4		2	1
Total			5	4

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

Product Id	product Name	Market	size	Price	count
Tv-123	tv	online	10	0.5	1
Tv-123	tv	online	5	0.5	1
Tv-123	tv	online	8	1	1
Tv-123	tv	online	6	1	1

Can anyone please help me on that

thanks!

Re: Generating dynamic rows and counting them

lbendlin — Sat, 24 Sep 2022 18:57:28 GMT

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.

Re: Generating dynamic rows and counting them

v-xiaosun-msft — Mon, 26 Sep 2022 10:00:31 GMT

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 () )

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

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.

Re: Generating dynamic rows and counting them

ybyb23 — Wed, 28 Sep 2022 13:15:36 GMT

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.

Re: Generating dynamic rows and counting them

ybyb23 — Wed, 28 Sep 2022 13:17:17 GMT

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 🙂