Counting the number of unique ID's/rows based on a criteria from one of the columns in the table

[amuola]

Hi,

Can someone explain how I can count the number of unique «Bookingnr» where the USERID has two different values in the “Transaction”?

e.g in this case the measure or a calculated column should return 1 (or something that indicate a hit for the function) for Bookingnumber 234 and 345 (since there is two different USERID for 1. Signature and 2. Signature), while the other two Bookingnumbers are 0/ False.

BookingnrTransactionUSERIDTimestamp

123	1. Signature	AAA	01.08.2018 00:00
123	2. Signature	AAA	02.08.2018 00:00
234	1. Signature	AAA	03.08.2018 00:00
234	2. Signature	BBB	04.08.2018 00:00
345	1. Signature	AAA	06.08.2018 00:00
345	2. Signature	BBB	07.08.2018 00:00
456	1. Signature	BBB	08.08.2018 00:00
456	2. Signature	BBB	09.08.2018 00:00

Appreciate any suggestions!

Regards

Amund

[Stachu]

try the code below, the logic is following
1) summarize bookings and users
2) group the summary and count nr of users per booking
3) filter only those where nr of users >1
4) count the outcome

Measure =
VAR BookingUser =
    GROUPBY (
        SUMMARIZE ( 'Table', 'Table'[Bookingnr], 'Table'[USERID] ),
        'Table'[Bookingnr],
        "NrOfUsers", COUNTAX ( CURRENTGROUP (), [USERID] )
    )
VAR OnlyMultiple =
    FILTER ( BookingUser, [NrOfUsers] > 1 )
RETURN
    COUNTROWS ( OnlyMultiple )

Cheers

Stachu

[amuola]

Hi,

Thanks! However, I am not able to split the number down on e.g department, time, other possible filters – I assume using ALL it will not take into account the filters; in this example I have added Dep and by splitting the measure down on Dep it shows the same value for all Dep.

Any suggestions?

Regards

Amund

[v-shex-msft · Accepted solution]

Hi @amuola,

Maybe you can try to use allselected function to replace all to enable filter effects on original tables.

Measure =
VAR BookingUser =
    GROUPBY (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Transaction] IN { "1. Signature", "2. Signature" }
            ),
            [Bookingnr],
            [USERID]
        ),
        [Bookingnr],
        "NrOfUsers", COUNTAX ( CURRENTGROUP (), [USERID] )
    )
VAR OnlyMultiple =
    FILTER ( BookingUser, [NrOfUsers] > 1 )
RETURN
    COUNTROWS ( OnlyMultiple )

Regards,
Xiaoxin Sheng

[amuola]

Hi,

Thank you so much, this works on the data provided.

However, I forgot to include other activities within each booking nr which can be executed by different USERID’s. This make it a bit more complicated, or is it possible to modify the measure to take into account that the only Transactions it should check is “1. Signature” and “2. Signature”. I’m sorry that I didn’t include this in the first post.

BookingnrTransactionUSERIDTimestamp

123	ActivityY	AAA	01.08.2018 00:00
123	1. Signature	AAA	02.08.2018 00:00
123	Activity X	CCC	03.08.2018 00:00
123	2. Signature	AAA	04.08.2018 00:00
345	ActivityY	AAA	06.08.2018 00:00
345	1. Signature	BBB	07.08.2018 00:00
345	Activity X	DDD	08.08.2018 00:00
345	2. Signature	BBB	09.08.2018 00:00
789	ActivityY	AAA	10.08.2018 00:00
789	1. Signature	BBB	11.08.2018 00:00
789	Activity X	AAA	12.08.2018 00:00
789	2. Signature	AAA	13.08.2018 00:00

In this case only Booking nr 789 should return 1.

Again, appreciate any help!

Regards

Amund

[v-shex-msft]

Hi @amuola,

You can add filter to remove unmatched records before summarize, I modify Stachus's formula to add filter formula in it.

Measure =
VAR BookingUser =
    GROUPBY (
        SUMMARIZE (
            FILTER ( ALL ( 'Table' ), [Transaction] IN { "1. Signature", "2. Signature" } ),
            [Bookingnr],
            [USERID]
        ),
        [Bookingnr],
        "NrOfUsers", COUNTAX ( CURRENTGROUP (), [USERID] )
    )
VAR OnlyMultiple =
    FILTER ( BookingUser, [NrOfUsers] > 1 )
RETURN
    COUNTROWS ( OnlyMultiple )

Regards,

Xiaoxin Sheng