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 co...
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.

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

       

      • User2's avatar
        User2
        Frequent Visitor
        3 years ago

        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.