Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count a measure ActiveProductPath

Hi all,

 

I need help with the following issue,

 

I have a measure which calculates the ActiveProductPath from each customer, see https://www.dropbox.com/s/2l02agyrk7dzk40/ActiveProductPathVirtualv3.pbix?dl=0

 

I want to count the path for each day, for example, on 10th April 2019 there are 3 paths, A, B and A,B so the result should be:

 

Path  Number

A           1

B           1

A, B       1

 

Can someone help me?

 

Thanks in advance,

 

Cor

 

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, I create some sample data to test.

    Here is my test table:

    please take following steps:

    1)Create a measure:

    Measure = COUNTROWS(FILTER(SUMMARIZE('DummyFile',DummyFile[Customer],"Path2",[ActiveProductPath]),[Path2] in FILTERS('Table'[Value])))

    2)Create a table by ‘Enter Data’:

    3)Use a date slicer to filter and the result shows:

    4)The result can also be shown as matrix to display the count of A/B/A,B for all dates:

    Here is my test pbix file

    pbix 

     

    Best Regards,

    Giotto Zhi

  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    For your confusion, I extract ‘summarize’ of the measure I provided and add a specified date to create a calculated table:

    Table 2 = CALCULATETABLE(SUMMARIZE('DummyFile',DummyFile[Customer],"Path2",[ActiveProductPath]),FILTER('ref Ref_Date','ref Ref_Date'[FullDate]=DATE(2019,4,30)))

    FILTERS ('Table'[Value]) means choosing the value you selected, like this:

    And [Path2] IN FILTERS ( 'Table'[Value] ) means checking whether the value of [Path2] is equal to ‘A’ or ‘B’ as the above chosen value.

    And then count the return table rows, using a ‘Card’ visual to show the original measure:

    When you choose [value] as columns and Measure as Values in ‘Matrix’ visual, it shows:

     

    Best Regards,

    Giotto Zhi

6 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    According to your description, I create some sample data to test.

    Here is my test table:

    please take following steps:

    1)Create a measure:

    Measure = COUNTROWS(FILTER(SUMMARIZE('DummyFile',DummyFile[Customer],"Path2",[ActiveProductPath]),[Path2] in FILTERS('Table'[Value])))

    2)Create a table by ‘Enter Data’:

    3)Use a date slicer to filter and the result shows:

    4)The result can also be shown as matrix to display the count of A/B/A,B for all dates:

    Here is my test pbix file

    pbix 

     

    Best Regards,

    Giotto Zhi

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Giotto,

       

      Thank you very much! This is the solution I was looking for! 🙂

       

      Best regards,


      Cor

       

      v-gizhi-msft 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Giotto,

       

      Can you explain the measure Measure = COUNTROWS(FILTER(SUMMARIZE('DummyFile',DummyFile[Customer],"Path2",[ActiveProductPath]),[Path2] in FILTERS('Table'[Value]))) to me?

      I understand the part "SUMMARIZE('DummyFile',DummyFile[Customer],"Path2",[ActiveProductPath])", can you explain what "COUNTROWS(FILTER(" and FILTERS('Table'[Value]) does?

       

      Thanks in advance,

       

      Cor

       

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        Hi,

         

        For your confusion, I extract ‘summarize’ of the measure I provided and add a specified date to create a calculated table:

        Table 2 = CALCULATETABLE(SUMMARIZE('DummyFile',DummyFile[Customer],"Path2",[ActiveProductPath]),FILTER('ref Ref_Date','ref Ref_Date'[FullDate]=DATE(2019,4,30)))

        FILTERS ('Table'[Value]) means choosing the value you selected, like this:

        And [Path2] IN FILTERS ( 'Table'[Value] ) means checking whether the value of [Path2] is equal to ‘A’ or ‘B’ as the above chosen value.

        And then count the return table rows, using a ‘Card’ visual to show the original measure:

        When you choose [value] as columns and Measure as Values in ‘Matrix’ visual, it shows:

         

        Best Regards,

        Giotto Zhi

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Please, can you try to explain your requirement as I'm struggling to understand?

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Of course, Mariusz 😀

       

      I have the following measure:

      ActiveProductPath = CALCULATE(CONCATENATEX(VALUES(DummyFile[Product]);DummyFile[Product]; ", ";DummyFile[Product];ASC);
         FILTER(DummyFile;DummyFile[From] <= MAX('ref Ref_Date'[FullDate]));
         FILTER(DummyFile; DummyFile[To] >= max('ref Ref_Date'[FullDate]) ) )
       
      That measure calculates for every date the "path" of active products.
       
      Now I want for each day the number of the several paths. The problem is that I have to count a "measure" and I don't know how to do that.
       
      For example date 29-03-2019, there a 3 paths, A, B and A,B so the end result should be:
       
      Path Number
      A 1
      B 1
      A, B 1
       
      I want to use the technique for another dataset for approximately 6.000 different "paths"....
       
      Do you understand the issue?
       
      Regards,
       
      Cor