cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tarunPoweBI
New Member

How to perform filter, grouping in one query and then getting MAX value from all the rows

I am new to PowerBI and resuing a graph used by the team. The below table is the screenshot of the table which we get after clicking 'Show as a table', the option we get on top right of a graph in PowerBI.

I am stuck in a very simple thing. I just want a MAX value and a MIN Value from the three columns marked in below image (Actuas 2022, Actuals 2022, Actuals, 2023). 

I will use the two values (Max and Min) to show line on the graph.

Please help me with the DAX query

TablesSS.png

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @tarunPoweBI,

It seems like you want to extract the min/max value from aggregate values. For this senecio, I think you can create variables to summary these field values and you can use min/max functions to compare these aggregated fields values.

formual =
VAR _act2021 =
    CALCULATE (
        SUM ( Table[Actuals 2021] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2022 =
    CALCULATE (
        SUM ( Table[Actuals 2022] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2023 =
    CALCULATE (
        SUM ( Table[Actuals 2023] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
RETURN
    MAX ( MAX ( _act2021, _act2022 ), _act2023 )

formual2 =
VAR _act2021 =
    CALCULATE (
        SUM ( Table[Actuals 2021] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2022 =
    CALCULATE (
        SUM ( Table[Actuals 2022] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2023 =
    CALCULATE (
        SUM ( Table[Actuals 2023] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
RETURN
    MIN ( MIN ( _act2021, _act2022 ), _act2023 )

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

1 REPLY 1
v-shex-msft
Community Support
Community Support

HI @tarunPoweBI,

It seems like you want to extract the min/max value from aggregate values. For this senecio, I think you can create variables to summary these field values and you can use min/max functions to compare these aggregated fields values.

formual =
VAR _act2021 =
    CALCULATE (
        SUM ( Table[Actuals 2021] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2022 =
    CALCULATE (
        SUM ( Table[Actuals 2022] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2023 =
    CALCULATE (
        SUM ( Table[Actuals 2023] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
RETURN
    MAX ( MAX ( _act2021, _act2022 ), _act2023 )

formual2 =
VAR _act2021 =
    CALCULATE (
        SUM ( Table[Actuals 2021] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2022 =
    CALCULATE (
        SUM ( Table[Actuals 2022] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
VAR _act2023 =
    CALCULATE (
        SUM ( Table[Actuals 2023] ),
        'Table',
        VALUES ( 'Table'[ReportingDate] )
    )
RETURN
    MIN ( MIN ( _act2021, _act2022 ), _act2023 )

Regards,

Xiaoxin Sheng

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.