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