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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
m4xon
Helper III
Helper III

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.