How to get unique values(materials)

[reemon007]

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.

[Anonymous · Accepted solution]

Hi @reemon007 ,

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]

Hello @reemon007 ,

create a measure as the following

DistinctCountMeasure = DISTINCTCOUNT(YourTable[Material])

[reemon007]

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]

HEllo @reemon007 ,

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

[reemon007]

Hello @Idrissshatila 

This is the breakdown.

Only count as Released if all status is Released and only count in Unreleased if all status is blank otherwise Partial Released.