Distincount with multiple condition

[Wajdi]

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)
  
  

  Device	Work Style	date	Time
  A	10	10/1/2022	8:00 AM
  A	10	10/1/2022	9:00 AM
  A	10	10/1/2022	10:00 AM
  A	10	10/1/2022	11:00 AM
  A	10	10/1/2022	12:00 PM
  A	10	10/1/2022	1:00 PM
  A	10	10/1/2022	2:00 PM
  A	10	10/1/2022	3:00 PM
  A	10	10/1/2022	4:00 PM
  A	10	10/1/2022	5:00 PM
  A	10	10/1/2022	6:00 PM
  A	10	10/1/2022	7:00 PM
  A	10	10/1/2022	8:00 PM
  A	10	10/2/2022	10:00 AM
  A	0	10/2/2022	12:00 PM
  A	15	10/2/2022	2:00 PM
  A	10	10/2/2022	4:00 PM
  A	15	10/2/2022	6:00 PM
  B	15	10/1/2022	8:00 AM
  B	15	10/1/2022	9:00 AM
  B	15	10/1/2022	10:00 AM
  B	15	10/1/2022	11:00 AM
  B	15	10/1/2022	12:00 PM
  B	15	10/1/2022	1:00 PM
  B	15	10/1/2022	2:00 PM
  B	15	10/1/2022	3:00 PM
  B	15	10/1/2022	4:00 PM
  B	15	10/1/2022	5:00 PM
  B	15	10/1/2022	6:00 PM
  B	15	10/1/2022	7:00 PM
  B	15	10/1/2022	8:00 PM
  C	10	10/1/2022	2:00 PM
  C	10	10/1/2022	3:00 PM
  C	10	10/1/2022	4:00 PM
  C	10	10/1/2022	5:00 PM
  C	10	10/1/2022	6:00 PM

  
  
  
  

[Anonymous · Accepted solution]

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]

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.

[Wajdi]

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