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
    Moderator

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
    Moderator
  • Hi how to get this value

    Select  count(*) , Flag from   [dbo].[Tablename]   A

    Group by Gender

    Expected Result 

    I need to count as well group by also. So foe example I need to show count of MALE & FEMALE employee.

    Gender             Count

    MALE                10

    FEMALE             20 


    Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question)

    Monday, June 12, 2017 1:36 PM
  • use only the AGE and put Gender in the column or row list and the calculate will split them automatically in rows or columns. Otherwise you need two measures, Total Male, Total Female.
    Monday, June 12, 2017 4:51 PM
  • I don't have age column . I just want below result. Both male and female is in Gender column I just need to count how name male /female emp is in the table.  

    Gender             Count

    MALE                10

    FEMALE             20 


    Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question)

    Tuesday, June 13, 2017 12:14 PM