Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Work Number.
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 )
RETURN
CALCULATE(
DISTINCTCOUNT( WO[SN] ),
ModelFilter,
WorkFilter
)
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.
Cheers!
Hi @caloyboy81,
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" ) )
Best regards,
Yuliana Gu
Hi Yuliana,
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.
The Power BI measure shows this as 2 desktops and 2 laptops.
For devices with 2 work orders, there are 70 devices:
Power BI shows this as 7 devices only:
Regards,
Carlo
Hi Yuliana,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |