Filter with count function within a measure

[MonK]

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

[MonK]

thank you very much!

[Alf94]

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]

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 · Accepted solution]

@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.