Measure

[Anonymous]

Hi there,

I have 2 columns of data; Product & Minimum order quantity. However sometimes I can have mission data in column #3 and I want to be able to count those value of a product that are either with data, or blank, if the same product has both a zero & number value. The goal is to count up the number of items that are not uniformly containing the data they should [Apple & Orange], without being skewed by products that are completely blank [Strawberry]. 

Apple	50
Apple	0
Apple	0
Orange	0
Orange	20
Strawberry	0
Strawberry	0

[amitchandak · Accepted solution]

@Anonymous , Create a new colum

column =
var _cnt_0 = countx(filter(table,[Product] =earlier([Product]) && [Qty]=0),[Product])
var _cnt = countx(filter(table,[Product] =earlier([Product]),[Product])
return 
if(_cnt_0 = 0 || _cnt =0,"Uniform","Non Uniform")

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create measure like DAX below.

Measure1 =
VAR _CountAll =
    COUNTROWS ( FILTER ( Table1, Table1[Product] = MAX ( Table1[Product] ) ) )
VAR _CountZero =
    COUNTROWS (
        FILTER (
            Table1,
            Table1[Product] = MAX ( Table1[Product] )
                && Table1[quantity] = 0
        )
    )
VAR _CountNoZero =
    COUNTROWS (
        FILTER (
            Table1,
            Table1[Product] = MAX ( Table1[Product] )
                && Table1[quantity] <> 0
        )
    )
RETURN
    IF (
        _CountZero = _CountAll
            || _CountNoZero = _CountAll,
        "Uniformly",
        "Not Uniformly"
    )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Create a new colum

column =
var _cnt_0 = countx(filter(table,[Product] =earlier([Product]) && [Qty]=0),[Product])
var _cnt = countx(filter(table,[Product] =earlier([Product]),[Product])
return 
if(_cnt_0 = 0 || _cnt =0,"Uniform","Non Uniform")

[Fowmy]

@Anonymous 

Can you show what is the expected output from the sample ou have given?
And, you can include more data with your sample for clear understanding.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Hopeful output is to show "2 products have incomplete data [minimum value & zero value recorded], 1 product is a flat zero in minimum order values entered"

[Fowmy]

@Anonymous 

Try this as  a measure:

Find Missing = 
var crow =COUNTROWS('Table')
var czero = 
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[Value] = 0
    )
return
IF(
     crow = czero ||
    (ISBLANK(czero)),
    BLANK(),
    "Missing"
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn