Add a calculated column that count number of rows based on an existing column

[wes-shen-poal]

Hi there,

I am very new to DAX, and I'm assuming this would be a very easy DAX formula that I just don't know.

I want to add a calculated column (highlight yellow) which gives me the count of VehicleIDs (or rows) based on the Ship Voyage column

Can someone kindly assist?

Thanks in advance.

Wes

[Anonymous]

Hi there,
We hope you can help us.! 😄 

Is it possible to make a formula where the first number of two will be changed to “1” and the second one to “0” based on characteristics seen in the first row.
Example: 
"if customer name or category or customer number is mentioned several times?"

We have tried to create a formula. but we have not solved the problem yet, hope you understand the problem. 

Calculated column =CALCULATE(COUNT(Append1[category]);ALLEXCEPT(Append1;Append1[category];Append1[customer number];Append1[dateyear]))  

Customer number	Customer name	Customer Category	Category	Dateyear	Calculated Column 1
1273	Peter Hansen	1253568942	PersonbogCPR	2019	1
1331	Anne Jensen	123456789	PersonbogCPR	2019	2
1331	Anne Jensen	123456789	PersonbogCPR	2019	2
1349	Poul West	15423697	PersonbogCPR	2019	1

[v-qiuyu-msft · Accepted solution]

Hi @wes-shen-poal,

You can create a calculated column like this:

Vehicles Onboard = CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[Ship Voyage]=EARLIER(Table1[Ship Voyage])))

Or you can create a measure like below:

Measure = CALCULATE(COUNTROWS('Table1'),FILTER(ALL('Table1'),'Table1'[Ship Voyage]=MAX('Table1'[Ship Voyage])))

You can download attached pbix file to have a look.

Best Regards,
Qiuyun Yu

[wes-shen-poal]

Hi @v-qiuyu-msft

Thank you so much for your help.

Wes