Forum Discussion

dexter's avatar
dexter
Icon for Helper II rankHelper II
7 years ago
Solved

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 A...
Need Help
  • ofirk's avatar
    ofirk
    7 years ago

    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:

ofirk's avatar
ofirk
Icon for Resolver II rankResolver II
7 years ago

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's avatar
dexter
Icon for Helper II rankHelper II
7 years ago

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