Forum Discussion

Wajdi's avatar
Wajdi
Frequent Visitor
3 years ago
Solved

Distincount with multiple condition

hello All  i have the below table telling me if the device was connected from office/vpn,remote... using colomn Value each hour each day  0 means Device Not seen Online anywhere ; 5 means - See...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Wajdi,

    I'm confused about your description, can you please share some more about the detailed description of these conditions?

    How to Get Your Question Answered Quickly 

    In addition, you can also try to use the following calculated column formula to check and remark the device status based on the date and device name:

    Status = 
VAR workRange =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', [Date], [Device], [Work Style] ),
        FILTER (
            'Table',
            [Time] >= TIME ( 8, 0, 0 )
                && [Time] <= TIME ( 20, 0, 0 )
                && [Work Style] <> 0
                && [Device] = EARLIER ( 'Table'[Device] )
                && [Date] = EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    SWITCH (
        COUNTROWS ( workRange ),
        3, "Hybrid",
        2,
            IF (
                COUNTROWS ( FILTER ( workRange, [Work Style] = 5 ) ) = 0,
                "Remote",
                "Hybrid"
            ),
        1,
            IF (
                COUNTROWS ( FILTER ( workRange, [Work Style] IN { 10, 15 } ) ) = 0,
                "Office",
                "Remote"
            ),
        "Offline"
    )

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Wajdi,

I'm confused about your description, can you please share some more about the detailed description of these conditions?

How to Get Your Question Answered Quickly 

In addition, you can also try to use the following calculated column formula to check and remark the device status based on the date and device name:

Status = 
VAR workRange =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', [Date], [Device], [Work Style] ),
        FILTER (
            'Table',
            [Time] >= TIME ( 8, 0, 0 )
                && [Time] <= TIME ( 20, 0, 0 )
                && [Work Style] <> 0
                && [Device] = EARLIER ( 'Table'[Device] )
                && [Date] = EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    SWITCH (
        COUNTROWS ( workRange ),
        3, "Hybrid",
        2,
            IF (
                COUNTROWS ( FILTER ( workRange, [Work Style] = 5 ) ) = 0,
                "Remote",
                "Hybrid"
            ),
        1,
            IF (
                COUNTROWS ( FILTER ( workRange, [Work Style] IN { 10, 15 } ) ) = 0,
                "Office",
                "Remote"
            ),
        "Offline"
    )

Regards,

Xiaoxin Sheng

Wajdi's avatar
Wajdi
Frequent Visitor
3 years ago

thank you @v-shex-msft  for your reply,
for each day i want to always have a 3 distincounting kpi (how many device was working office,vpn and remote....),
for exple to could say that device name was connected office on one specefic day,it should have work style at least 10 one time during 8am to 8pm and i should not see it getting 10,15,0 for the same day,for the above table i can say that on 10/1/2022 i have 2 device connected vpn (device a + c)  and one device connected remote ,on  10/2/2022 i have one device connected hybrid,

Attached i added a pbix with some simple measure (https://www.dropbox.com/t/oKD9C7KjfHBiWOEU)with excell data source to better explain pls check it and let me know if still there is some uncl eaer point ...