Count a measure ActiveProductPath

[Anonymous]

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

[v-gizhi-msft · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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

[Anonymous]

Hi Giotto,

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

Best regards,

Cor

@v-gizhi-msft 

[Mariusz]

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]

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