Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
m4xon
Helper II
Helper II

Count and group how many time occurs

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
2134
313
42
587
665
790
832
954

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

 

 

QuarterNoAnswer
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014829OK
2023-1REL0014879OK
2023-1REL0014879OK
2023-1REL0014879OK
2023-1REL0014879OK
2023-1REL0014879OK
2023-1REL0014879OK
2023-1REL0014879OK
2023-1REL0014879OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015227OK
2023-1REL0015267OK
2023-1REL0015267OK
2023-1REL0015267OK
2023-1REL0015267OK
2023-1REL0015267OK
2023-1REL0015267OK
2023-1REL0015267OK
2023-1REL0015422OK
2023-1REL0015422OK
2023-1REL0015422OK
2023-1REL0015422OK
2023-1REL0015422OK
2023-1REL0015422OK
2023-1REL0015422OK
2023-1REL0015422OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016272OK
2023-1REL0016464OK
2023-1REL0016464OK
2023-1REL0016464OK
2023-1REL0016464OK
2023-1REL0016464OK
2023-1REL0016464OK
2023-1REL0016464OK
2023-1REL0016464OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0016851OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017226OK
2023-1REL0017552OK
2023-1REL0017552OK
2023-1REL0017552OK
2023-1REL0017552OK
2023-1REL0017552OK
2023-1REL0017552OK
2023-1REL0017552OK
2023-1REL0017552OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014829OK
2023-2REL0014879OK
2023-2REL0014879OK
2023-2REL0014879OK
2023-2REL0014879OK
2023-2REL0014879OK
2023-2REL0014879OK
2023-2REL0014879OK
2023-2REL0014879OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015227OK
2023-2REL0015267OK
2023-2REL0015267OK
2023-2REL0015267OK
2023-2REL0015267OK
2023-2REL0015267OK
2023-2REL0015267OK
2023-2REL0015267OK
2023-2REL0015422OK
2023-2REL0015422OK
2023-2REL0015422OK
2023-2REL0015422OK
2023-2REL0015422OK
2023-2REL0015422OK
2023-2REL0015422OK
2023-2REL0015422OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016272OK
2023-2REL0016464OK
2023-2REL0016464OK
2023-2REL0016464OK
2023-2REL0016464OK
2023-2REL0016464OK
2023-2REL0016464OK
2023-2REL0016464OK
2023-2REL0016464OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0016851OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017226OK
2023-2REL0017552OK
2023-2REL0017552OK
2023-2REL0017552OK
2023-2REL0017552OK
2023-2REL0017552OK
2023-2REL0017552OK
2023-2REL0017552OK
2023-2REL0017552OK
2023-2REL0012299OK
2023-2REL0012299OK
2023-2REL0012299OK
2023-2REL0012299OK
2023-2REL0012299OK
2023-2REL0012299OK
2023-2REL0012299OK
2023-2REL0014088OK
2023-2REL0014088OK
2023-2REL0014088OK
2023-2REL0014088OK
2023-2REL0014088OK
2023-2REL0014088OK
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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] )

1.PNG

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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] )

1.PNG

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.