Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure

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
Apple0
Apple0
Orange0
Orange20
Strawberry0
Strawberry0
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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's avatar
    v-xicai
    6 years ago

    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.

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Fowmy
        Super User
        6 years ago

        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

         



  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
    v-xicai
    Community Support
    6 years ago

    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.