Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Hi,
Based on that 2 data tables that you have shared, show the exact result you are expecting.
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 |
I don't see any duplicates in that new sample data?
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?
What is the expected outcome? drop 3 and 8 ?
the short of it yes, would be to drop those from the % affected, but keep thier counts in # of incidents
Sorry but that's too convoluted. I think my original proposal has a higher level of truthiness.
User | Count |
---|---|
85 | |
85 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |