06-14-2018 09:43 PM
Hi Power BI Community,
I have been using Power BI Desktop for a while now but quite new to DAX. I have what looks like an easy problem but I can't seem to make it work despite looking through different examples all over the net.
I am working on a single table with 3 columns. The table is called WORKORDERS or WO for short.
The columns are:
SN for Serial Number
Model - can have Desktop, Laptop, Server, etc.
Sample table is:
SN Model Work Number
12345 Desktop WO1001
67890 Desktop WO1002
11121 Laptop WO1003
13141 Desktop WO1004
12345 Desktop WO1005
Basically, as devices breakdown a Work Order is created to have the device fixed.
The Work Order number is always unique. the SN is also unique but it can appear multiple times in a table because devices can break down multiple times.
I'm trying to create a DAX Measure to know how many devices broke down 1 time, 2 times, 3 times, and so on and so forth.
I was thinking of having a measure for the number of times devices break. Meaning:
1 measure for devices breaking down 1 time
1 measure for devices breaking down 2 times
1 measure for devices breaking down 3 times
and so on ... i will have a measure per Model as well.
So, for the first DAX Measure I wrote:
Desktop WO 1 =
VAR ModelFilter = FILTER( WO, WO[Model] = "Desktop" )
VAR WorkFilter = FILTER( WO, COUNT( WO[Work Number] ) = 1 )
DISTINCTCOUNT( WO[SN] ),
I have also tried changing the WorkFilter to WO[SN] instead of WO[Work Number] but I still get a (Blank) result.
Hope you can help and thanks in advance.
06-18-2018 12:45 AM
Please modify above measure as:
Desktop WO 1 = CALCULATE ( DISTINCTCOUNT ( WO[SN] ), FILTER ( WO, CALCULATE ( COUNT ( WO[Work Number] ), ALLEXCEPT ( WO, WO[Model], WO[SN] ) ) = 1 && WO[Model] = "Desktop" ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
06-19-2018 10:35 PM
Thanks for writing back. I can't seem to make the DAX return the correct value. I'll try to create a new pbix with the sample data in the coming days.
The number of devices that should be returned by the measure is not what I was expecting.
06-24-2018 10:32 PM
I have uploaded the .pbix and the Excel spreadsheet datasource in here.
The spreadsheet has 2 tabs. The data and the summary tab. The .pbix file uses the data tab. The summary is only to show the expected result.
In the summary tab, you'll be able to see that there are 3 devices with 1 work order.
- 1 desktop and
- 2 laptops.
The Power BI measure shows this as 2 desktops and 2 laptops.
For devices with 2 work orders, there are 70 devices:
- 12 are desktops
- 58 are laptops
Power BI shows this as 7 devices only:
- 1 desktop
- 6 laptops