Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |