Find Min value and overlap value using DAX table

[Murali777]

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.

[Jihwan_Kim]

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]

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]

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

[Murali777]

Hi Jihwan Sir,

It is not showing the second row. 

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

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

[Murali777]

Thank you Jihwan 🙂

[Murali777]

@Jihwan_Kim Hello sir, pls help me on this.