Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to get unique values(materials)

Hi,

 

I'm new to PowerBi and DAX. Would like to ask for help on how to get unique count of the materials depending on the status.

 

Example: material 1028530, if all rows is Released then count as 1 else, if has other status the count it in Partially Released, 

And only count Unreleased if all rows in PRNo is blank.

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    You can try formula like below:

     

    result_ = 
VAR table_ =
    SUMMARIZE (
        YourTable,
        YourTable[Material],
        "Status",
            IF (
                COUNTROWS ( FILTER ( YourTable, YourTable[PRStatus] = "Released" ) )
                    = COUNTROWS ( YourTable )
                    && NOT ( ISBLANK ( FIRSTNONBLANK ( YourTable[PRNo], 1 ) ) ),
                "Released",
                IF (
                    COUNTROWS ( FILTER ( YourTable, YourTable[PRStatus] = "Partially Released" ) ) > 0,
                    "Partially Released",
                    IF (
                        COUNTROWS ( FILTER ( YourTable, ISBLANK ( YourTable[PRNo] ) ) )
                            = COUNTROWS ( YourTable ),
                        "Unreleased",
                        BLANK ()
                    )
                )
            )
    )
RETURN
if(HASONEVALUE(YourTable[PRStatus]),CALCULATE ( COUNTROWS ( table_ ) ),BLANK())

     

     

    Best Regards,
    Adamk Kong

     

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    You can try formula like below:

     

    result_ = 
VAR table_ =
    SUMMARIZE (
        YourTable,
        YourTable[Material],
        "Status",
            IF (
                COUNTROWS ( FILTER ( YourTable, YourTable[PRStatus] = "Released" ) )
                    = COUNTROWS ( YourTable )
                    && NOT ( ISBLANK ( FIRSTNONBLANK ( YourTable[PRNo], 1 ) ) ),
                "Released",
                IF (
                    COUNTROWS ( FILTER ( YourTable, YourTable[PRStatus] = "Partially Released" ) ) > 0,
                    "Partially Released",
                    IF (
                        COUNTROWS ( FILTER ( YourTable, ISBLANK ( YourTable[PRNo] ) ) )
                            = COUNTROWS ( YourTable ),
                        "Unreleased",
                        BLANK ()
                    )
                )
            )
    )
RETURN
if(HASONEVALUE(YourTable[PRStatus]),CALCULATE ( COUNTROWS ( table_ ) ),BLANK())

     

     

    Best Regards,
    Adamk Kong

     

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

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello Anonymous ,

     

    create a measure as the following

    DistinctCountMeasure = DISTINCTCOUNT(YourTable[Material])
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Idrissshatila ,
      Thank you for your time. Result is below. But the expected result is 5 Released, 3 Partial, and 1 Unreleased. 

      Full source.

       

       

      • Idrissshatila's avatar
        Idrissshatila
        Super User
        2 years ago

        HEllo Anonymous ,

         

        but as per your data here, for example the distinct material for released are 8 not 5 as you can see.