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,

    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