Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to create a measure that gives me the count of ID's (Column A) where column B = 'xxx' and it occurs 3 or more times. For the following table, the measure would return 2(IDs 1 and 3 have XXX 3 or more times). However, I'm struggling with the DAX for the measure. Any ideas? Assume table is named MyTable.
ID | ColumnB |
1 | XXX |
2 | ABC |
1 | XXX |
1 | XXX |
3 | XXX |
3 | XXX |
3 | XXX |
Solved! Go to Solution.
I was able to get it to work in two steps. Creating a table, then a measure off of that table. Still trying to figure out how to do it all in one step. Added a column in the grouping to get people by provider that have 3 or more values of XXX in the ColumnB table.
IC =
SUMMARIZE(
FILTER(
'Table',
'Table'[ColumnB] = "XXX"),
'Table'[ID],
'Table'[ProviderName],
"tc", COUNT('Table'[ID])
)
MEASURE4 =
CALCULATE(COUNTROWS(IC), IC[tc] >=3)
Hi @TCavins ,
Create 2 measures as below:
Count1 =
VAR _count1 =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[ColumnB] = MAX ( 'Table'[ColumnB] )
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER ( ALL ( 'Table' ), _count1 >= 3 && 'Table'[ID] = MAX ( 'Table'[ID] ) )
)
Count =
SUMX ( VALUES ( 'Table'[ID] ), 'Table'[Count1] )
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@TCavins try this
Measure =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
GROUPBY (
CALCULATETABLE (
'Table',
FILTER ( VALUES ( 'Table'[ColumnB] ), 'Table'[ColumnB] IN { "XXX" } )
),
'Table'[ID],
"X", COUNTX ( CURRENTGROUP (), [ColumnB] )
),
[X] >= 3
)
)
or
Measure2 =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ADDCOLUMNS (
SUMMARIZE (
FILTER ( 'Table', 'Table'[ColumnB] = "XXX" ),
'Table'[ID],
'Table'[ColumnB]
),
"X", CALCULATE ( COUNT ( 'Table'[ColumnB] ) )
),
[X] >= 3
)
)
I was able to get it to work in two steps. Creating a table, then a measure off of that table. Still trying to figure out how to do it all in one step. Added a column in the grouping to get people by provider that have 3 or more values of XXX in the ColumnB table.
IC =
SUMMARIZE(
FILTER(
'Table',
'Table'[ColumnB] = "XXX"),
'Table'[ID],
'Table'[ProviderName],
"tc", COUNT('Table'[ID])
)
MEASURE4 =
CALCULATE(COUNTROWS(IC), IC[tc] >=3)
Hi @TCavins ,
Glad to hear it.
Could you pls mark the reply as answered to close it?
If possible,would you pls also mark other correct replies as well.Thus others could also check them.
Thanks in advance.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@TCavins well done. Make sure you understand the secrets of SUMMARIZE if you are to perform aggregation inside SUMMARIZE.
https://www.sqlbi.com/articles/all-the-secrets-of-summarize/
I'm not getting the correct results with either of those.
@TCavins my solution is based on the data you gave me and as you can see the measure is performing as desired in the sample dataset you gave. Is there something you did not include?
I needed a count of IDs that have have XXX 3 or more times.
The measure should return 2 because there are 2 IDs that have XXX at least 3 times in ColumnB.
@TCavins change to this
Measure2 =
CALCULATE (
DISTINCTCOUNT('Table'[ID]),
FILTER (
ADDCOLUMNS (
SUMMARIZE (
FILTER ( 'Table', 'Table'[ColumnB] = "XXX" ),
'Table'[ID],
'Table'[ColumnB]
),
"X", CALCULATE ( COUNT ( 'Table'[ColumnB] ) )
),
[X] >= 3
)
)
@TCavins , A new measure
countx(filter(summarize(Table, Table[ID],"_1" ,Count(Table[columnb])) ,[_1] >=3) ,[ID])
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |