Showing percentage of affected devices each day

[Ghost_Of_Casper]

Hello,

I'm looking for assistance with building a report in PowerBI where i can show the percentage of known devices that have an active case each day. I have a list with the devices and issue open/close dates, A refence table of all the known devices and a standard DimDate table that is related to the issue list open date, 

This should be possible as i can easily do this in Excel with minimal effort but i'm banging my head here when trying to do this in Power BI. Any assitance would be helpful

For refernce my tables are as such examples

Device	Count
Unit Type 1	15
Unit Type 2	6
Unit Type 3	45
Unit Type 4	3

Issue #	Opened	Closed	Device
1	4/6/2023	4/8/2023	Unit Type 2
2	4/6/2023	4/9/2023	Unit Type 3
3	4/10/2023	4/10/2023	Unit Type 2
4	4/11/2023	4/13/2023	Unit Type 4
5	4/30/2023	5/3/2023	Unti Type1
6	4/30/2023	5/2/2023	Unit Type 1
7	4/30/2023		Unti Type 4

[Ashish_Mathur]

Hi,

Based on that 2 data tables that you have shared, show the exact result you are expecting.

[lbendlin]

add a disconnected dates table and a couple of measures to compute the impacted issues for each date.

see attached

[Ghost_Of_Casper]

Thank you Ibendlin, This has helped immesly, but when I increase the data size I do run into an issue where I need to only count duplicates once per day, Is there an adjustment that needs to be made to the DAX that would accomidate this?

Example is when there are reports for the same issue in the same day I would like to only count it as one and not count each instance. the data size increase includes a location so it would look simmilar to this

Issue #	Opened	Closed	Device	Location
1	4/6/2023	4/8/2023	Unit Type 2	Room a
2	4/6/2023	4/9/2023	Unit Type 3	Room b
3	4/10/2023	4/10/2023	Unit Type 4	Room a
4	4/10/2023	4/13/2023	Unit Type 4	Room a
5	4/10/2023	4/13/2023	Unit Type 1	Room a
6	4/12/2023	5/2/2023	Unit Type 1	Room c
7	4/30/2023	5/4/2023	Unit Type 4	Room b
8	5/1/2023	5/1/2023	Unit Type 2	Room a
9	5/1/2023	5/5/2023	Unit Type 2	Room a

[lbendlin]

I don't see any duplicates in that new sample data?

[Ghost_Of_Casper]

The example dupllicates are #3&4 and #8&9 it can be seen that for #3 it was opened and closed the same day while #4 was the same room and issue opened the same day that #3 was (later in the day) but closed out later, simmilar with the second set.

What would be the best way to account for the issue only being counted once for that type of occurance?

[lbendlin]

What is the expected outcome?  drop 3 and 8 ?

[Ghost_Of_Casper]

the short of it yes, would be to drop those from the % affected, but keep thier counts in # of incidents

[lbendlin]

Sorry but that's too convoluted.  I think my original proposal has a higher level of truthiness.