Forum Discussion

bvanevr's avatar
bvanevr
Advocate II
8 years ago

DAX count with count filter

I am needing help with a complex formula needed for error checking.  This all needs to be done in DAX (not in the Query editor) as there are dynamic filters in the report that need to impact these co...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi bvanevr

 

Try this

 

Go to Modelling Tab and press the "NEW TABLE" button

 

New Table =
FILTER (
    SUMMARIZE (
        TableName,
        TableName[Sold to],
        "ID Count", DISTINCTCOUNT ( TableName[ID] )
    ),
    [ID Count] > 1
)
  • bvanevr's avatar
    bvanevr
    Advocate II
    8 years ago

    Thanks for the fast response

     

    I was able to create the table, but it does not allow for dynamic filtering from within the report.  For example if the user does not want status = X  In that case the count for Sold-to = 1 would not be 3 but 2  and Sold to = 2 would be 1 and would be filtered out.

     

    Returns counts of

    1          | 2

    2         | 1

    3         | 1

     

    Filter for > 1 -- Sold to # 1 has 2 associated ID's which is an error

    1          | 2

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      bvanevr

       

      Hi, Use this Measure in the Table. Then you can add a slicer for Status

       

      ID Count =
CALCULATE (
    DISTINCTCOUNT ( TableName[ID] ),
    FILTER (
        VALUES ( TableName[Sold to] ),
        CALCULATE ( DISTINCTCOUNT ( TableName[ID] ) > 1 )
    )
)