Finding MAX and MIN of a summarized table in DAX

user_19647056 0
Viewed 315

I'm working on creating a measure in DAX to find the MAX and MIN of a specific sum of values. I have three distinct Name (X,Y,Z) and different DateTimeStamp. I want the DAX to first check if the selected Names in the pivot table exist for each DateTimeStamp then calculate the sum of the values and if for a specific DateTimeStamp the Name does not exist then put a BLANK for it. Finally I want to find the MAX and MIN of the calculated sums. My Table with 3 columns and the last column is what I'm looking for

I found a solution using summarize function that is presented below but it only works for a condition without any filter and if I choose my pivot table to filter based on the names (X,Y,Z) it does not work properly.

MINX(
SUMMARIZE(TABLE, TABLE[DateTimeStamp],
 "SumValue", IF(COUNT(TABLE[Name])=[NameCount],SUM(Table[Value]),BLANK())),
 [SumValue]
)

where NameCount measure is defined as follow:

CALCULATE(DISTINCTCOUNT(TABLE[Name]),ALL(TABLE))
dax powerbi-desktop powerbi powerquery
3 Answers

Hello Full Solution updated:

DEFINE
    TABLE IDEAL_Backtest_AccountDetails =
        SELECTCOLUMNS (
            {
                ( dt"2022-07-28T10:00:00", "X", 157 ),
                ( dt"2022-07-28T10:00:00", "Y", 113 ),
                ( dt"2022-07-28T10:00:00", "Z", 155 ),
                ( dt"2022-07-28T10:01:00", "Z", 130 ),
                ( dt"2022-07-28T10:01:00", "Z", 173 ),
                ( dt"2022-07-28T10:02:00", "X", 107 ),
                ( dt"2022-07-28T10:02:00", "Y", 103 ),
                ( dt"2022-07-28T10:02:00", "Z", 100 ),
                ( dt"2022-07-28T10:03:00", "X", 188 ),
                ( dt"2022-07-28T10:03:00", "Z", 106 ),
                ( dt"2022-07-28T10:04:00", "X", 111 ),
                ( dt"2022-07-28T10:04:00", "Y", 106 ),
                ( dt"2022-07-28T10:04:00", "Z", 174 )
            },
            "DateTimeStamp", [Value1],
            "Name", [Value2],
            "Value", [Value3]
        )

ResultSet_01:

Image_01

MEASURE IDEAL_Backtest_AccountDetails[NameCount] =
    CALCULATE ( DISTINCTCOUNT ( IDEAL_Backtest_AccountDetails[Name] ), ALL ( IDEAL_Backtest_AccountDetails ) )

ResultSet_02:

Image_02

Final Result should look like this:

EVALUATE
ROW (
    "MIN",
        MINX (
            ADDCOLUMNS (
                SUMMARIZE (
                    IDEAL_Backtest_AccountDetails,
                    IDEAL_Backtest_AccountDetails[DateTimeStamp]
                ),
                "SumofEachDateTime",
                    IF (
                        CALCULATE ( DISTINCTCOUNT ( IDEAL_Backtest_AccountDetails[Name] ) ) = [NameCount],
                        CALCULATE ( SUM ( IDEAL_Backtest_AccountDetails[Value] ) ),
                        BLANK ()
                    )
            ),
            [SumofEachDateTime]
        ),
    "MAX",
        MAXX (
            ADDCOLUMNS (
                SUMMARIZE (
                    IDEAL_Backtest_AccountDetails,
                    IDEAL_Backtest_AccountDetails[DateTimeStamp]
                ),
                "SumofEachDateTime",
                    IF (
                        CALCULATE ( DISTINCTCOUNT ( IDEAL_Backtest_AccountDetails[Name] ) ) = [NameCount],
                        CALCULATE ( SUM ( IDEAL_Backtest_AccountDetails[Value] ) ),
                        BLANK ()
                    )
            ),
            [SumofEachDateTime]
        )
)

Final Result:

enter image description here

user_194690880

Thanks for the response, I also found another easy solution to my problem. Here is my solution:

=MINX(
SUMMARIZE(IDEAL_Backtest_AccountDetails, IDEAL_Backtest_AccountDetails[DateTimeStamp],
 "SumEquity", IF(COUNTROWS(IDEAL_Backtest_AccountDetails)=calculate(DISTINCTCOUNT(IDEAL_Backtest_AccountDetails[Symbol]), ALLSELECTED()),SUM(IDEAL_Backtest_AccountDetails[AccountEquity]),BLANK())),
 [SumEquity]
)
user_196470560

Your expression I'd rewrite as following.

VAR distNames=COUNTROWS(VALUES(TABLE[Name])
VAR tbl=
     ADDCOLUMNS(
        SUMMARIZE(TABLE, TABLE[DateTimeStamp])
        ,"SumValue",SUM(Table[Value])
        ,"CountValues", CALCULATE(COUNTROWS(VALUES(TABLE[Name]))
     )

VAR filterdTbl =
    FILTER(
        tbl
        ,[CountValues]=distNames
    )

RETURN
    MINX(
      filterdTbl
      ,[SumValue]
    )

Then it can be another way. The idea is following

  1. Get all Name unique values.
  2. Filter the table by compare using except().
  3. Then define Min in a filtered table.

I didn't check the measure, but you can try. If you and a table in addition to your picture, then I can check the measure.

   VAR myValues=VALUES(myTable[Names])
   VAR filterdTbl=
        FILTER(
             myTable
            ,IF(
                ISBLANK(
                       Except(
                            myValues
                            ,CALCULATETABLE(
                                  VALUES(myTable[Names])
                                 ,ALLEXCEPT(myTable,myTable[DateTimeStamp])
                             )
                       )
                 )
                 ,TRUE()
                 ,FALSE()
               )
          )
   RETURN
        MINX(
            filterdTbl
            ,CALCULATE(
                     SUMX(
                         filterdTbl
                        ,CALCLULATE(
                                 SUM(myTable[VALUES])
                                 ,ALLEXCEPT(myTable,myTable[DateTimeStamp])
                         )
                      )
             )
        )
user_190423790
Related