Calculate Lines with same condition

[srehman]

Hi Team,
Need your thoughts i am doing some mistake not achieving desired results by using DAX.
please provide your advice.

I have total 10841 Lines or rows which is combination of following condition.

I am using Power Pivot DAX to calculate those Lines in which SKU has blank() and DIS both condition on same location.

I have written some dax.

CountALLLines:=COUNTROWS(SOHP) -->working correct total lines 10841

location check DIS:=CALCULATE([countline],SOHP[Condition Code]="DIS") -->
working correct total lines 51

location check Good:=CALCULATE([countline],FILTER(SOHP,SOHP[Condition Code]=BLANK())) -->working correct total lines 10634
Now i want to check

i want to check (SKU has blank() and DIS both condition on same location.

But following formula not calculating correct

===============================================================================================
following both formula not working
===========================================================================================
location check Good&DIS:=CALCULATE([countline],FILTER(SOHP,SOHP[Condition Code]="DIS" && SOHP[Condition Code]=BLANK()))
or
location check Good&DIS(try2):=CALCULATE([countline],SOHP[Condition Code]="DIS" ,SOHP[Condition Code]=BLANK())

================================================================================================

SOH Condition check.xlsx
	P	Q
3	Condition Code	Count of SKU
4	(blank)	10634
5	DAM	2
6	DIS	51
7	INS	48
8	NCD	94
9	QAH	10
10	RET	2
Sheet2

Expected Result

SOH Condition check.xlsx
	P	Q	R	S
14	SKU	Location	(blank)	DIS
15	13481550	KAR085	1	1
16	16026620	KAR190	1	1
17	47774130	EO03210C	3	1
Sheet2

 

[jorismoerings]

Hi,

Not an easy answer with the information provided.
Working from the assumption:

1. your data file holds 10841 lines; and
2. the condition column is a column which holds 1 condition per line; and
3. you don't show what the fieldname [countline] is; and
4. [countline] does actually count the lines; and
5. you want to count the line where the condition meets 1 of the criteria requested.

assuming: you're looking for an "OR" function.

So if above is correct, try this:
location check Good&DIS(try2):=CALCULATE([countline],OR(SOHP[Condition Code]="DIS" ,SOHP[Condition Code]=BLANK()))

which should come up with 10685 lines (10634 from blanks and 51 from DIS)

Hope this helps

 

[srehman]

Hi,
Thanks for your time & effort really appreciate it.

Apologies for less info.

Please review now

Not an easy answer with the information provided.
Working from the assumption:

1. your data file holds 10841 lines; and ---> Yes
2. the condition column is a column which holds 1 condition per line; and---> Yes (Blank) means good condition
3. you don't show what the fieldname [countline] is; and-----> here i have counted all rows
4. [countline] does actually count the lines; and------> Yes
5. you want to count the line where the condition meets 1 of the criteria requested. ---> I want to check those SKU lines or rows which must have condition of Blank() and DIS ( Which is i derived from power query 51 lines of Blank() & 50 Lines of DIS.
6. How can i check unique SKU falls in both condition & total number of lines.

Example data

2 lines of Good stock and 1 DIS we need to count , total 3 lines of 1 SKU

SOH Condition check.xlsx
	A	B	C	D	E	F	G	H	I	J
3	SKU	SKU Description	Obsolete?	Location	Location Zone	Sub Zone	Qty On Hand	Pallet Type	Move Date	Condition Code
4	11073160	G 2800 AUS	N	KAR002	40BLK01	BULK	180	PLT	43815
5	11073160	G 2800 AUS	N	KAR020	40BLK01	BULK	96	PLT	43811	DIS
6	11073160	G 2800 AUS	N	KAR046	40BLK01	BULK	216	PLT	43815
7	11073160	G 2800 AUS	N	KAR080	40BLK01	BULK	143	PLT	43808	INS
Sheet6

assuming: you're looking for an "OR" function.

So if above is correct, try this:
location check Good&DIS(try2):=CALCULATE([countline],OR(SOHP[Condition Code]="DIS" ,SOHP[Condition Code]=BLANK()))

which should come up with 10685 lines (10634 from blanks and 51 from DIS)

Hope this helps

 

[srehman]

Hi I have done some calculation now , but i cannot view in pivot table value field

 

[jorismoerings]

Hi,

Not sure what you aiming for because you've changed from number of Records to Unique SKU but

to get the result on:

1. all lines: location check Good&DIS(try2):=CALCULATE( COUNTROWS(SOHP) ,OR(SOHP[Condition Code]="DIS" ,SOHP[Condition Code]=BLANK()))
2. Unique SKU: location check Good&DIS(try2):=CALCULATE( DISTINCTCOUNT(SOHP[SKU]) ,OR(SOHP[Condition Code]="DIS" ,SOHP[Condition Code]=BLANK()))

 

[srehman]

Hi,

Not sure what you aiming for because you've changed from number of Records to Unique SKU but

to get the result on:

1. all lines: location check Good&DIS(try2):=CALCULATE( COUNTROWS(SOHP) ,OR(SOHP[Condition Code]="DIS" ,SOHP[Condition Code]=BLANK()))
2. Unique SKU: location check Good&DIS(try2):=CALCULATE( DISTINCTCOUNT(SOHP[SKU]) ,OR(SOHP[Condition Code]="DIS" ,SOHP[Condition Code]=BLANK()))

Yes its working could you assist when i dropping this measures in pivot table value header can't view any numbers. While all DAX calculations are correct.

 

[jorismoerings]

Hi,

Don't know how because i've tested it on my end and everything works like it should.

 

[srehman]

Now it is working tried in other sheet all good. Thanks