Forum Discussion
calculating the count based on two conditional checks..
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
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:
7 Replies
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)
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:
Hi dexter
You may refer to below measure:
DeliveredA1Count = COUNTROWS ( FILTER ( 'Table2', 'Table2'[Level] = "A1" && SEARCH ( "Delivered", Table2[Comments], 1, 0 ) > 0 ) ) + 0Regards,
Cherie
