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
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 )
    )
)

 

    • bvanevr's avatar
      bvanevr
      Advocate II
      8 years ago

      Thanks Zubair,

       

      I was able to replicate your solution.  

       

      Would you be able to help with 2 phase of my question?  I need to be able to provide users with an item to click that would act as a filter to only display those records >1 independent of the dynamic filter for Status.  Idealy a table with one value "Click to Filter Mult IDs".

       

      In the past I used a 2nd table to map values, but since we are generating the count on the fly so the count is not a column in the ingested table, I cant create a relationship on the count.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        bvanevr

         

        Hi,

         

        You can add an ID_Count calculated column as well. Then you can use it a slicer for Users to select records>1

         

        ID_Count =
CALCULATE (
    DISTINCTCOUNT ( TableName[ID] ),
    FILTER ( TableName, TableName[Sold to] = EARLIER ( TableName[Sold to] ) )
)