topic Re: Row Count Sum in DAX Commands and Tips

Row Count Sum

andicornejo12 — Fri, 02 Feb 2024 17:10:12 GMT

Hello Community,

I am stuck here. The request is to have a 1 in every row of a table built in the report, but the sum gives the total of those rows.

For showing the "1" everywhere, I added a custom column equals to 1 to the fact table, I then summarized the column by distinctcount but my result for the total is 1 also (I would need here the total row count like in column 3). The third column is a measure

Row Count = SUM('_CiQ Milestone'[RowCount]) that gives me the right total but is not showing me a 1 in each row. Can someone help me with this request?

Thank you!

Re: Row Count Sum

talespin — Fri, 02 Feb 2024 17:43:52 GMT

hi andicornejo12 ,

Is this the end result you are looking for?

Re: Row Count Sum

andicornejo12 — Mon, 05 Feb 2024 10:04:24 GMT

Hi talespin

Thank you so much for your answer.

Yes, what you are showing in the picture is what I need to show.

However, the problem is that AWB number can be repeated many times in the fact table, for example the AWB number 14 in your example table can show up twice in the table depending on the dimension you want to break it into. Therefore your solution is giving me the same result as the third column in my example 😞

Re: Row Count Sum

talespin — Mon, 05 Feb 2024 12:56:25 GMT

hi andicornejo12 ,

Please use this measure, replace it with your column. If there are other columns in your visual, might need to modify measure. Ignore the custom column in second screenshot(not required).

MCount =

IF(HASONEFILTER( TestTbl4[AWBNo] ), 1, COUNT( TestTbl4[AWBNo] ) )

Re: Row Count Sum

Dangar332 — Mon, 05 Feb 2024 13:47:25 GMT

hi, andicornejo12

i think Distinctcount() function work as you want
make a measure

measure = distinctcount(tablename[awb number])

Re: Row Count Sum

andicornejo12 — Mon, 05 Feb 2024 13:55:26 GMT

hi Dangar332

Thanks for your reply. I already tried distinctcount. The problem with this, is the second column of my table above. It doesnt give me the total, the total is shown as 1 as well 😞

Re: Row Count Sum

andicornejo12 — Mon, 05 Feb 2024 13:59:56 GMT

hi talespin

Thanks again for your help.

Your formula almost works is showing a one and the total.

However, the request is a bit more complicated, in my example I used AWB Number but the request is that row count should work with any column in the table. See with AWB version below, is not working anymore (your solution is RowCount1 in red)

Re: Row Count Sum

Dangar332 — Mon, 05 Feb 2024 14:03:05 GMT

hi, andicornejo12

try below measure

countofid = IF(ISINSCOPE('Table'[id]),1,SUM('Table'[value]))

Re: Row Count Sum

talespin — Mon, 05 Feb 2024 14:14:40 GMT

hi andicornejo12 ,

"However, the request is a bit more complicated, in my example I used AWB Number but the request is that row count should work with any column in the table."

Are the columns from same table or can be from different table?

Try this if columns in visual are from same table. Test the total count.

IF( ISFILTERED(TestTbl4), 1, COUNT( TestTbl4[AWBNo] ) )

Re: Row Count Sum

andicornejo12 — Mon, 05 Feb 2024 14:49:56 GMT

hi Dangar332

This is almost perfect.!!! However only works with AWB Number if I substitute AWB number column with AWB version it doesnt work anymore :(.

Is there a way to use this formula to build a table from any column ?? see below first image is AWB number and is perfect, second image though is AWB version instead of AWB number and it doesnt work anymore. I need to have the AWB number in the table

Re: Row Count Sum

andicornejo12 — Mon, 05 Feb 2024 14:54:17 GMT

hi talespin

I appreciate so much the follow up. Thank you!!

However this formula is giving me teh same result as the previous one, a one in each row with a total but if I bring in AWB version instead of AWB number is not working anymore. I need to have AWB number in the table 😞

Re: Row Count Sum

talespin — Mon, 05 Feb 2024 14:57:29 GMT

Hi andicornejo12

If possible, please share pbix file with all sensitive data removed.

Re: Row Count Sum

Dangar332 — Mon, 05 Feb 2024 15:06:33 GMT

hi, andicornejo12

not sure but just try

nested if

countofid = IF( ISINSCOPE('Table'[awb number]), 1, IF( ISINSCOPE('Table'[awb vers no]), 1,sum('Table'[awb vers no]) ) )

Re: Row Count Sum

talespin — Thu, 08 Feb 2024 12:51:00 GMT

I don't think this is the solution. Requester wanted this to work no matter what column is in visual(same table to another table).

Re: Row Count Sum

andicornejo12 — Fri, 09 Feb 2024 00:38:46 GMT

talespin no is not the full solution as you said, the request is that it works with any column but I dont think there is other way :S