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 differ...
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,

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