Unique count measure

[rigosakh]

Hi all,

I'm migrating from Excel to Power BI and struggling with turning some excel calculations into DAX format.

A screenshot that sort of shows how the data is structured is below.

The measure that I need should set "unique count" to 1 if the "value" hasn't been recorded before at a certain PK , but if the same value is recorded at the same on the next hour, it should be assigned 0. 

Eg AAA at PK 1 at 8am is 1

                         at 9am 0

at 10am there is a new value at PK 1 - GGG and it is assigned a unique count 1 

at 11am AAA appears at PK 5 and is counted as 1 again

I tried to solve this by using CONCATENATing columns "date" "pk" and "value" but then got stuck on how to evaluate each row against all previous rows without using too much of the computing power (the data that I work with is usually 50000-100000 rows).

Would really appreciate if someone could help me with this

Cheers! 

[v-huizhn-msft · Accepted solution]

Hi @rigosakh,

I reproduce your scenario and get expected result. Please follow the steps below.

1. In desktop, please create a calculated column using the formula, I assume the table name as 'Table2'.

Column =
RANKX (
    FILTER (
        Table2,
        Table2[PK] = EARLIER ( Table2[PK] )
            && Table2[Value] = EARLIER ( Table2[Value] )
    ),
    Table2[Time],
    ,
    ASC,
    DENSE
)

2. Create another calculted column to get Unique count based on the column above.

Unique Count =
IF (
    Table2[Value] = BLANK (),
    0,
    IF (
        Table2[Column]
            = CALCULATE ( MIN ( Table2[Column] ), ALLEXCEPT ( Table2, Table2[Value] ) ),
        1,
        0
    )
)

You will get desired result as follows.



Best Regards,
Angelia

[rigosakh]

Hi @v-huizhn-msft , this is exactly what I needed! Thank you so much, I didn't think about approaching it this way 

[rigosakh]

Hi @v-huizhn-msft , this is exactly what I needed! Thank you so much, I didn't think about approaching it this way