Forum Discussion

MonK's avatar
MonK
Regular Visitor
3 years ago
Solved

Filter with count function within a measure

Hi all,

 

I have a table with the following columns, among some other columns:

 

ID

Type

 

Now I need a measure that will count the number of distinct IDs that occur more than 1x when filtered by a particular type. I want to ignore all the ones that appear only once here. Additionally, a Datediff function is to be added as a filter, but that had worked.

 

I tried this combination, but the red line unfortunately gave an error message:

 

Calculate(distinctcount(table[ID]),

Filter(table, table[Type] = "abc"),

Filter(table,Datediff(Table[Date1],Table[Date2] <= 'What-if'[Value],

Filter(countx(ID) > 1))

 

Can someone help me here? Probably it's very easy, but I could not find a solution.

thanks a lot

  • Alf94's avatar
    Alf94
    3 years ago

    MonK,

     

    What (I think) you have to do is the following:

     

    Measure = 
    CALCULATE(
        DISTINCTCOUNT( 'Table'[ID] ),
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                'Table',
                'Table'[type],
                'Table'[ID]
                ),
                "count ID", CALCULATE( COUNT( 'Table'[ID] ) )
            ),
            [count ID] > 1
        ),
        Table[type] = "abc",
        Datediff(Table[Date1], Table[Date2]) <= 'What-If'[Value]
    )

     

    Let me know if this works.

4 Replies

  • Alf94's avatar
    Alf94
    Solution Supplier
    3 years ago

    Hi MonK ,

     

    Can you please try the following?

     

    Measure = 
    CALCULATE(
        DISTINCTCOUNT( 'Table'[ID] ),
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                'Table',
                'Table'[type],
                'Table'[ID]
                ),
                "count ID", CALCULATE( COUNT( 'Table'[ID] ) )
            ),
            [count ID] > 1
        )
    )

     

    You can add as filters the type you want to consider and your datediff function.

     

    If I answered your question, please mark my post as a solution.

    Best,

    • MonK's avatar
      MonK
      Regular Visitor
      3 years ago

      thanks a lot.

       

      I tried the measure and used my filters after this:

       "count ID", CALCULATE( COUNT( 'Table'[ID] ) )

       is this correct in this place before the last ")"? than I got the result as it would be with all Ids, not with the >1.

      [count ID] > 1

       Do you have an idea here? thank you very much for your efforts Alf94 

      • Alf94's avatar
        Alf94
        Solution Supplier
        3 years ago

        MonK,

         

        What (I think) you have to do is the following:

         

        Measure = 
    CALCULATE(
        DISTINCTCOUNT( 'Table'[ID] ),
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                'Table',
                'Table'[type],
                'Table'[ID]
                ),
                "count ID", CALCULATE( COUNT( 'Table'[ID] ) )
            ),
            [count ID] > 1
        ),
        Table[type] = "abc",
        Datediff(Table[Date1], Table[Date2]) <= 'What-If'[Value]
    )

         

        Let me know if this works.