Forum Discussion

caloyboy81's avatar
caloyboy81
Regular Visitor
8 years ago

CALCULATE with COUNT filter.

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!

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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

    • caloyboy81's avatar
      caloyboy81
      Regular Visitor
      8 years ago

      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.

    • caloyboy81's avatar
      caloyboy81
      Regular Visitor
      8 years ago

      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.

      • 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

       

      Regards,

      Carlo