Forum Discussion
calculating the count based on two conditional checks..
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,
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)
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..
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, 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.
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: