Count with filter

[User2]

Hey guys,

I am new to Power BI and I need help with the following problem.

I have a column1 with journeys and in the second column I counted them using the aggregation count.

In the third column I switched the beginning and the end of the journey.

Now, in column 4 I want to lookup how many times I can find the exact journey back.

Is there any way to do it?

Thanks, Peter

Journey	no. of journey	journey back	no. of journey back
Frankfurt - Hamburg	this column is no problem	Hamburg - Frankfurt	this should be 0
London - Glasgow		Glasgow - London	this should be 1
Glasgow - London		London - Glasgow	same
Rome - Budapest		Budapest - Rome	0
Frankfurt - Hamburg		Hamburg - Frankfurt	0

[Anonymous]

Hi @User2 ,

Like this?

You can create a measure like this :

no.of journey back = var _back= CALCULATE(COUNTROWS('Table'),FILTER('Table',SELECTEDVALUE('Table'[Journey back])in ALL('Table'[Journey])))
return if(_back<>0,_back,0)

I also attached my pbix file, you can refer it.

Best regards,

Community Support Team Selina zhu

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

[User2]

Hi @Anonymous ,

unfortunately, it doesn't yield the result I want to achieve.

Somehow, I am unable to upload files, so I uploaded images of my data and the result from your measure.

table in excelresult in power bi

I want to create a delta, which is simply the difference between # of journeys and # of journeys back.

In my pbix file, Frankfurt - Hamburg is counted 2 times and Hamburg - Frankfurt is counted 8 times.
So, the first row should look like this:

Journey	# journeys	journey back	# journeys back	delta
Frankfurt - Hamburg	2	Hamburg - Frankfurt	8	-6

Any other idea how I can achieve this result?

Appreciate your effort!

[Anonymous]

Hi @User2 ,

If your table can present this one-to-one correspondence, then it is easy to do the calculations you want.

just create a measure to calculate,like this

value = SUM('Table'[number of Journey ])-SUM('Table'[number of back])

Since the calculation is for the same row, it may be can't meet your needs when there is no correspondence between journey and journey back.

Best regards,

Community Support Team Selina zhu

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

[User2]

Hi @Anonymous ,

the problem is not how I calculate my value, the problem is how I can extract the number of journeys back out of the column "no. of journeys". All I receive is the same values for both columns.

Example:

I only get the values if column "journey" and "journey back" are identical, e.g. "F - F" and "F - F" but if it is "F- H" to "H- F" I cannot extract the value. 

How did you count the number of journeys back? I don't get how I can count them without receiving the exact same values as for the column "journey".

Thanks for your help.

[User2 · Accepted solution]

I solved the problem by duplicating the "journey" column, then grouping it and finally adding a new calculated column in the original table using a Lookupvalue.