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 filter...
  • 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.

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.