DAX formula help...

[Matty]

Hi Team,

Looking for suggestions as to how others would tackle the following issue...

Data as follows:

Book1
	A	B	C	D	E
1	Shop	Month	Quantity	Value	Missing
2	A	1	10	24	No
3	A	2	5	0	Yes
4	A	3	15	34	No
5	B	1	0	0	No
6	B	2	20	48	No
7	B	3	35	0	Yes
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=IF(AND(C2<>0,D2=0),"Yes","No")

Column E is there to illustrate what I want the measure to do. Note that I do NOT want to use a calculated column.

With Month removed, the matrix would return the following:

Book1
	G	H	I	J
1	Shop	Quantity	Value	Missing
2	A	30	58	Yes
3	B	55	48	Yes
Sheet1

The idea here being that if Quantity is <> 0 and Value = 0 for any records (rows) for a given Shop in the underlying data, then 'Yes' should show at the aggregated level.

I'll continue having a play myself to see if I can get something to work, but any help would be welcome.

Cheers,

Matty

 

[Matty]

This seems to do the job:

Missing Check = 
IF (
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER ( Table1, Table1[Quantity] <> 0 && Table1[Value] = 0 )
    ) > 0,
    "Yes",
    "No"
)

Cheers,

Matty