Forum Discussion

Burak83_'s avatar
Burak83_
Regular Visitor
3 years ago

Count based on multiple criteria DAX

Hi Gents,

I would like to count distinct values based on the combination of multiple columns.(In sample Location+Item+Status) I added my table as data model to count distinct values but it does not give correct results. I know how to distinct count using helper column or excel function like =IF(COUNTIFS($C$2:C2,C2,$B$2:B2,B2)>1,0,1)but is there any way to do it in power pivot ? as calculated column or measure? Thanks for your help and comments.

LocationItemStatusDistinct Count
KitchenPlatesMoved
1​
KitchenSpoonsStayed
1​
SaloonTVMoved
1​
SaloonChairMoved
2​
SaloonChairMoved
2​
BathroomMirrorStayed
1​

 

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Burak83_ 

    Try to plot a table visual with columns:Location, Item, Status, and a measure like:
    Distinct Count = COUNTROWS(TableName)
     
    in case of issue, could you provide some sample data?
  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    3 years ago

    This code does it. 

    Distinct Count 2 = 
VAR varItems = SELECTEDVALUE('Table'[Item])
VAR varLocation = SELECTEDVALUE('Table'[Location])
VAR varStatus = SELECTEDVALUE('Table'[Status])
VAR varFilteredTable =
        CALCULATETABLE(
            'Table',
            'Table'[Item] = varItems
                && 'Table'[Location] = varLocation
                && 'Table'[Status] = varStatus
        )
VAR Result = COUNTROWS(varFilteredTable)
RETURN 
    Result
    • Burak83_'s avatar
      Burak83_
      Regular Visitor
      3 years ago

      Hi Edhans,

      Thanks for the solution. However, since I am using powerpivot it does not support selectedvalue. Is there any other equivalent formula that I can use? like IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>)

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Yes. Just use this instead of SELECTEDVALUE() in PowerPivot. It gives the same results.

         

        IF ( HASONEVALUE( <columnName> ), VALUES( <columnName> ), <alternateResult> )

        The first one would be:

        IF ( HASONEVALUE( Table[Item] ), VALUES( Table[Item] ) )

        Note that SELECTEDVALUE is coming to PowerPivot. See New DAX Functions in Excel Data Models and Power Pivot (office.com)