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 - Seen at office ;
  • 10 means - Seen on VPN ;
  • 15 means - Seen remotely

    The logic i want to build is :
  • Distincount all devices connected from office using below 
    for each device,during time range from 8am to 8pm ,Work Style field should be at least 5 (at any time during 8am to 8pm)ANDWork Style should be <> 15 AND10 AND0 during 8am to 8pm
  • Distincount all devices connected hybrid (remote + vpn+office) using below logic 
    for each device,during time range from 8am to 8pm ,Work Style = (at least 10 or 15 at any time during 8am to 8pm ) AND Work Style = 5(at any time during 8am to 8pm)
  • Distincount all devices connected Remote (remote + vpn) using below logic 
    for each device,during time range from 8am to 8pm ,Work Style= (at least 10 or 15 at any time during 8am to 8pm ) and Work Style <> 5(at any time during 8am to 8pm)

    DeviceWork StyledateTime 
    A1010/1/20228:00 AM
    A1010/1/20229:00 AM
    A1010/1/202210:00 AM
    A1010/1/202211:00 AM
    A1010/1/202212:00 PM
    A1010/1/20221:00 PM
    A1010/1/20222:00 PM
    A1010/1/20223:00 PM
    A1010/1/20224:00 PM
    A1010/1/20225:00 PM
    A1010/1/20226:00 PM
    A1010/1/20227:00 PM
    A1010/1/20228:00 PM
    A1010/2/202210:00 AM
    A010/2/202212:00 PM
    A1510/2/20222:00 PM
    A1010/2/20224:00 PM
    A1510/2/20226:00 PM
    B1510/1/20228:00 AM
    B1510/1/20229:00 AM
    B1510/1/202210:00 AM
    B1510/1/202211:00 AM
    B1510/1/202212:00 PM
    B1510/1/20221:00 PM
    B1510/1/20222:00 PM
    B1510/1/20223:00 PM
    B1510/1/20224:00 PM
    B1510/1/20225:00 PM
    B1510/1/20226:00 PM
    B1510/1/20227:00 PM
    B1510/1/20228:00 PM
    C1010/1/20222:00 PM
    C1010/1/20223:00 PM
    C1010/1/20224:00 PM
    C1010/1/20225:00 PM
    C1010/1/20226:00 PM



  • 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

3 Replies

  • 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 ... 

    • Wajdi's avatar
      Wajdi
      Frequent Visitor
      3 years ago

      Awsome Anonymous  i tested it and it's working perfectly fine,one last think,can u just told me how i can add the fouthr count for VPN only (those having  scrore of 10 or zero full day )  ,thank you in advance.