Forum Discussion

Murali777's avatar
Murali777
Helper III
4 years ago
Solved

Find Min value and overlap value using DAX table

Hello,

 

I have already creatd the summarize table with below columns (step1), And my
step 2 is to find the min value from the different strategy and same name.
Step 3 : Final out put is, Total Number of strategy from my first step and overlap count from the second step.

 

Step 3 is my expected output in the calculated table.

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your message.

    It is for creating a new table, and not for a measure.

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if my DAX formula meets your need, but I created the below picture and the attached pbix file based on the sample.

    It is for creating a new table.

     

     

    New Table = 
VAR _newtable =
    FILTER (
        Data,
        COUNTROWS ( FILTER ( Data, Data[Name] = EARLIER ( Data[Name] ) ) ) = 2
    )
VAR _minvaluetable =
    CALCULATETABLE (
        Data,
        TREATAS (
            GROUPBY (
                _newtable,
                Data[Date],
                Data[Name],
                "@MinValue", MINX ( CURRENTGROUP (), Data[SumofValues] )
            ),
            Data[Date],
            Data[Name],
            Data[SumofValues]
        )
    )
RETURN
    ROW (
        "AppleTotal", COUNTROWS ( FILTER ( Data, Data[Strategy] = "Apple" ) ),
        "MangoTotal", COUNTROWS ( FILTER ( Data, Data[Strategy] = "Mango" ) ),
        "Overlap", COUNTROWS ( _minvaluetable )
    )
    • Murali777's avatar
      Murali777
      Helper III
      4 years ago

      Hi Jihwan,

       

      Thank you for your coding. It almost working, i'm at the final stage. In step 3 i need to add the total row. Kindly help me on this final step.

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Please check the attached file and the below DAX formula to create a new table.

         

        New Table =
VAR _newtable =
    FILTER (
        Data,
        COUNTROWS ( FILTER ( Data, Data[Name] = EARLIER ( Data[Name] ) ) ) = 2
    )
VAR _minvaluetable =
    CALCULATETABLE (
        Data,
        TREATAS (
            GROUPBY (
                _newtable,
                Data[Date],
                Data[Name],
                "@MinValue", MINX ( CURRENTGROUP (), Data[SumofValues] )
            ),
            Data[Date],
            Data[Name],
            Data[SumofValues]
        )
    )
VAR _appletotal =
    { COUNTROWS ( FILTER ( Data, Data[Strategy] = "Apple" ) ) }
VAR _mangototal =
    { COUNTROWS ( FILTER ( Data, Data[Strategy] = "Mango" ) ) }
VAR _overlap =
    { COUNTROWS ( _minvaluetable ) }
VAR _minvalueappletotal =
    {
        SUMX ( FILTER ( _minvaluetable, Data[Strategy] = "Apple" ), Data[SumofValues] )
    }
VAR _minvaluemangototal =
    {
        SUMX ( FILTER ( _minvaluetable, Data[Strategy] = "Mango" ), Data[SumofValues] )
    }
VAR _totaloverlap =
    { SUMX ( _minvaluetable, Data[SumofValues] ) }
RETURN
    UNION (
        ROW (
            "AppleTotal", _appletotal,
            "MangoTotal", _mangototal,
            "Overlap", _overlap
        ),
        ROW (
            "AppleTotal", _minvalueappletotal,
            "MangoTotal", _minvaluemangototal,
            "Overlap", _totaloverlap
        )
    )