Forum Discussion

User2's avatar
User2
Frequent Visitor
3 years ago
Solved

Count with filter

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

Journeyno. of journeyjourney backno. of journey back

Frankfurt - Hamburg

this column is no problemHamburg - Frankfurtthis should be 0
London - Glasgow Glasgow - Londonthis should be 1
Glasgow - London London - Glasgowsame
Rome - Budapest Budapest - Rome0
Frankfurt - Hamburg Hamburg - Frankfurt0
Need Help
  • User2's avatar
    User2
    3 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      User2
      Frequent Visitor
      3 years ago

      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# journeysjourney back# journeys backdelta
      Frankfurt - Hamburg2Hamburg - Frankfurt8-6

       

      Any other idea how I can achieve this result?

       

      Appreciate your effort!

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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