topic Re: Filter with count function within a measure in DAX Commands and Tips

Filter with count function within a measure

MonK — Tue, 04 Jul 2023 10:58:51 GMT

Hi all,

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

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

Re: Filter with count function within a measure

Alf94 — Tue, 04 Jul 2023 12:55:15 GMT

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,

Re: Filter with count function within a measure

MonK — Tue, 04 Jul 2023 14:56:42 GMT

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

Re: Filter with count function within a measure

Alf94 — Tue, 04 Jul 2023 15:02:41 GMT

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.

Re: Filter with count function within a measure

MonK — Wed, 05 Jul 2023 16:49:15 GMT

thank you very much!