none
DAX for tabular Model

    Question

  • I need to do a distinct count based on 2 flags for example count(distinct customerID) where gender = M and age = 30
    Thursday, July 24, 2014 5:50 PM

Answers

  • If you want these flag selections to be interactive and dynamic for your users, you should use slicers to filter the results of the measure.

    If you want to hard code these into a measure, then the following is the template you'll need to use.

    CALCULATE( DISTINCTCOUNT( '--table--'[Column] )

        , '--table--'[Gender] = "M"

        , '--table--'[Age] = 30

    )

    Thursday, July 24, 2014 7:24 PM
  • You can filter the rows applied to any aggregate function using the CALCULATE and optionally the FILTER function.  For example to get a distinct count of products with static filter criteria, the expression would look something like this:

    Count of Red Stock Products:=CALCULATE(
           DISTINCTCOUNT(Product[Name]),
           FILTER( ALL(Product), Product[Color] = "Red" && Product[SafetyStockLevel] = 500 )
    )

    This variation would apply slicers & filters:

    Count of Selected Products:=CALCULATE(
           DISTINCTCOUNT(Product[Name]),
           ALLSELECTED(Product)
    )


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Friday, July 25, 2014 4:41 AM

All replies

  • If you want these flag selections to be interactive and dynamic for your users, you should use slicers to filter the results of the measure.

    If you want to hard code these into a measure, then the following is the template you'll need to use.

    CALCULATE( DISTINCTCOUNT( '--table--'[Column] )

        , '--table--'[Gender] = "M"

        , '--table--'[Age] = 30

    )

    Thursday, July 24, 2014 7:24 PM
  • You can filter the rows applied to any aggregate function using the CALCULATE and optionally the FILTER function.  For example to get a distinct count of products with static filter criteria, the expression would look something like this:

    Count of Red Stock Products:=CALCULATE(
           DISTINCTCOUNT(Product[Name]),
           FILTER( ALL(Product), Product[Color] = "Red" && Product[SafetyStockLevel] = 500 )
    )

    This variation would apply slicers & filters:

    Count of Selected Products:=CALCULATE(
           DISTINCTCOUNT(Product[Name]),
           ALLSELECTED(Product)
    )


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Friday, July 25, 2014 4:41 AM