Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
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.
thank you very much!
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,
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
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |