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