Count and group how many time occurs

[m4xon]

Hello,

How to handle following problem.

I have a dataset and I have to count how many times the answer OK apears per each REL number and then count how many times occure each value. So as a result it should be like this:

Number of occurence:	Result
1 (one OK per REL appear 12 times)	12
2	134
3	13
4	2
5	87
6	65
7	90
8	32
9	54

To make it more difficult I need to be able to filter it by Quarter.

I have done it using PQ, but was wondering if its possible to do it directly in DAX somehow.

Thank you

Max

Quarter	No	Answer
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014829	OK
2023-1	REL0014879	OK
2023-1	REL0014879	OK
2023-1	REL0014879	OK
2023-1	REL0014879	OK
2023-1	REL0014879	OK
2023-1	REL0014879	OK
2023-1	REL0014879	OK
2023-1	REL0014879	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015227	OK
2023-1	REL0015267	OK
2023-1	REL0015267	OK
2023-1	REL0015267	OK
2023-1	REL0015267	OK
2023-1	REL0015267	OK
2023-1	REL0015267	OK
2023-1	REL0015267	OK
2023-1	REL0015422	OK
2023-1	REL0015422	OK
2023-1	REL0015422	OK
2023-1	REL0015422	OK
2023-1	REL0015422	OK
2023-1	REL0015422	OK
2023-1	REL0015422	OK
2023-1	REL0015422	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016272	OK
2023-1	REL0016464	OK
2023-1	REL0016464	OK
2023-1	REL0016464	OK
2023-1	REL0016464	OK
2023-1	REL0016464	OK
2023-1	REL0016464	OK
2023-1	REL0016464	OK
2023-1	REL0016464	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0016851	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017226	OK
2023-1	REL0017552	OK
2023-1	REL0017552	OK
2023-1	REL0017552	OK
2023-1	REL0017552	OK
2023-1	REL0017552	OK
2023-1	REL0017552	OK
2023-1	REL0017552	OK
2023-1	REL0017552	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014829	OK
2023-2	REL0014879	OK
2023-2	REL0014879	OK
2023-2	REL0014879	OK
2023-2	REL0014879	OK
2023-2	REL0014879	OK
2023-2	REL0014879	OK
2023-2	REL0014879	OK
2023-2	REL0014879	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015227	OK
2023-2	REL0015267	OK
2023-2	REL0015267	OK
2023-2	REL0015267	OK
2023-2	REL0015267	OK
2023-2	REL0015267	OK
2023-2	REL0015267	OK
2023-2	REL0015267	OK
2023-2	REL0015422	OK
2023-2	REL0015422	OK
2023-2	REL0015422	OK
2023-2	REL0015422	OK
2023-2	REL0015422	OK
2023-2	REL0015422	OK
2023-2	REL0015422	OK
2023-2	REL0015422	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016272	OK
2023-2	REL0016464	OK
2023-2	REL0016464	OK
2023-2	REL0016464	OK
2023-2	REL0016464	OK
2023-2	REL0016464	OK
2023-2	REL0016464	OK
2023-2	REL0016464	OK
2023-2	REL0016464	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0016851	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017226	OK
2023-2	REL0017552	OK
2023-2	REL0017552	OK
2023-2	REL0017552	OK
2023-2	REL0017552	OK
2023-2	REL0017552	OK
2023-2	REL0017552	OK
2023-2	REL0017552	OK
2023-2	REL0017552	OK
2023-2	REL0012299	OK
2023-2	REL0012299	OK
2023-2	REL0012299	OK
2023-2	REL0012299	OK
2023-2	REL0012299	OK
2023-2	REL0012299	OK
2023-2	REL0012299	OK
2023-2	REL0014088	OK
2023-2	REL0014088	OK
2023-2	REL0014088	OK
2023-2	REL0014088	OK
2023-2	REL0014088	OK
2023-2	REL0014088	OK

[v-shex-msft · Accepted solution]

HI @m4xon,

It sounds like you want to achieve two step aggregations to summary table records.

For this scenario, you can create a new table with category you want and white a measure formula with variable and SUMMARIZE function to apply the first level aggerate. Then you can extract current category value as conditions with iterator function COUNTX to summarized variable table records.

formula =
VAR currVal =
    MAX ( NewTable[Value] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'Table' ), [Answer] = "OK" ),
        [No],
        "Occur", COUNT ( 'Table'[Quarter] )
    )
RETURN
    COUNTX ( FILTER ( summary, [Occur] = currVal ), [No] )

Regards,

Xiaoxin Sheng

[m4xon]

Amazing, thank you! I have only removed ALLSELECTED to be able to filter based on [Quarter] on charts.