calculating the count based on two conditional checks..

[dexter]

I have created a sample table shown below.

ID   Product  Level  Comments

100  Laptop   A1     Dispatched

101  Laptop   A1     Dispatched,Delivered

102  Mobile   A3     Dispatched

103  PC       A2     Dispatched,returned

104  Mobile   A3     Dispatched,returned

105  PC       A3     Dispatched,Delivered

106  Laptop   A2     Delivered

107  Laptop   A2     Shipped

108  PC       A1     Delivered

109 PC      A1      Delivered

I am creating a new measure in the table as shown below which returns the count of the column(Level) which has value "A1" as shown below. Similarly created measures A2 Count which returns the count of the column(Level) which has value A2 and created A3 Count..

A1Count =
CALCULATE(
COUNTAX(
FILTER ( 'ProdData', 'ProdData'[Level] = "A1"),
'ProdData'[Level]
))

In the picture above, the table shows the product name and A1Count ,A2 Count,A3Count. It is counting and displaying for product Laptop how many times the Level A1 is repeated and shown in A1Count by using the above shown measure(A1Count).Similarly i have created A2Count and A3Count measures which counts how many times the level A2 and A3 is mentioned in Level column for each product.

Now my requirement is for each Product and Level , i want to count Comments column have the word "Delivered".

I'm expecting result as below. What is the possible best way ?

Product     A1Count  A2Count   A3Count  DeliveredA1Count   DeliveredA2Count     DeliveredA3Count

Laptop         2               2                                        1                              1                                   0

Mobile                                            2                      0                               0                                   0

PC                2              1                 1                      2                              0                                   1

[v-cherch-msft]

Hi @dexter

You may refer to below measure:

DeliveredA1Count =
COUNTROWS (
    FILTER (
        'Table2',
        'Table2'[Level] = "A1"
            && SEARCH ( "Delivered", Table2[Comments], 1, 0 ) > 0
    )
)
    + 0

Regards,

Cherie

[ofirk]

Hi,

If I understand your sample data correctly - in this case the result would be 3 because some product in each of the 3 levels has a comment that contains "Delivered".

How about this measure?

measure =

IF(COUNTX(FILTER(ProdData, [Level] = "A1" && SEARCH("Delivered", [Comments], 1, -1) > 0), [ID]) > 0, 1) + 

IF(COUNTX(FILTER(ProdData, [Level] = "A2" && SEARCH("Delivered", [Comments], 1, -1) > 0), [ID]) > 0, 1) + 

IF(COUNTX(FILTER(ProdData, [Level] = "A3" && SEARCH("Delivered", [Comments], 1, -1) > 0), [ID]) > 0, 1)

[dexter]

@ofirk, Please see my edited post. Hope i'm more clear now about what i'm expecting. The one which you showed is not the one i'm expecting..

[ofirk · Accepted solution]

Hi,

In this case, considering A1Count, A2Count and A3Count are measures you already created, I'd create the following additional measures:

DeliveredA1Count = COUNTX(FILTER(ProdData, [A1Count]>0 && SEARCH("Delivered", [Comments], 1, -1) > 0), [id]) 

DeliveredA2Count = COUNTX(FILTER(ProdData, [A2Count]>0 && SEARCH("Delivered", [Comments], 1, -1) > 0), [id]) 

DeliveredA3Count = COUNTX(FILTER(ProdData, [A3Count]>0 && SEARCH("Delivered", [Comments], 1, -1) > 0), [id]) 

The result is as you described:

[dexter]

@ofirk,  Along with the above result i was trying to create a new column which shows both A1Count,DeliveredA1Count result in a single column along with some separator and show 0(zero) for A1Count,A2Count,A3Count when there are no value to show.Similarly i'm creating a new column to show A2Count,DeliveredA2Count and A3Count...

Below are the steps i followed..

Select New Column from Home Ribbon: Include the below line to create a new column

A1DLCount = [A1Count] & "--" & [DeliveredA1Count]

Similarly created New Columns for A2DLCount and A3DLCount which shows both the results in single column 

A2DLCount = [A2Count] & "--" & [DeliveredA2Count]

A3DLCount = [A3Count] & "--" & [DeliveredA3Count]

When dragged the above columns in the table, the results are not as expected.

I was expecting results for the new Columns created as below

Product  A1Count A2Count  A3Count  DelivA1Count DeliverdA2Count DeliveredA3Count A1DLCount  A2DLCount A3DLCount

 Laptop       2              2          0                     1                           1                           0                     2--1                 2--1             0--0

 Mobile       0               0          2                    0                            0                          0                     0--0                 0--0             2--0

  PC             2               1          1                    2                            0                           1                     2--2                 1--0             1--1

Any inputs on the above would be helpful.

[ofirk]

Hi @dexter,

To solve this, add + 0 to your earlier measures, as v-cherch-msft suggested.

For example, 

A1Count = CALCULATE( COUNTAX( FILTER ( 'ProdData', 'ProdData'[Level] = "A1"), 'ProdData'[Level] )) + 0

DeliveredA1Count = COUNTX(FILTER(ProdData, [A1Count]>0 && SEARCH("Delivered", [Comments], 1, -1) > 0), [id]) + 0

(same for 2, 3)

Keep A1DLCount, A2DLCount and A3DLCount as they are (using measures, not columns).

The results should look like this:

   

[dexter]

@ofirk, Thanks for the detail explanantion. I have one more question on this. If user want to see the details of DeliveredA1Count
when clicked on the DeliveredA1Count value shown in A1DLCount column(for Laptop value is 2--1, when user click on
value 2, can we show details of A1Count in other table similarly when user click on value 1 i want to show the details of DeliveredA1COunt in the DetailsTable created side to this table). Basically i want to apply the onclick event for the values(or the hyperlink) shown in A1DLCount/A2DLCount/A3DLCount columns(which are the measures created) and when clicked on the value show the detailed information..Please advice.