Forum Discussion

patrickrw99's avatar
patrickrw99
Frequent Visitor
4 years ago
Solved

Convert Calculated Column to Measure, Need to use Row Context

Hi,   I would like to know how to convert the following DAX Calculated Column Formula:     Calculated Column = VAR ValueA=CALCULATE(SUM('DataSource1'[ValueA]),FILTER('DataSource1',SEARCH('Data...
Need Help
Tips and Tricks
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi patrickrw99 ,

     

    I create another measure, please try it and see if it works.

     

    SumMeasure = SUMX ( VALUES ( 'Data Mapping'[AttributeA] ), [Measure] )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi patrickrw99 ,

 

Please try the following formula:

 

Measure =
VAR ValueA =
    CALCULATE (
        SUM ( 'DataSource1'[ValueA] ),
        FILTER (
            ALLSELECTED ( 'DataSource1' ),
            SEARCH ( MAX ( 'Data Mapping'[AttributeA] ), 'DataSource1'[AttributeA], 1, 0 )
                && 'DataSource1'[AttributeB] = MAX ( 'Data Mapping'[AttributeB] )
        )
    )
VAR ValueB =
    CALCULATE (
        SUM ( 'DataSource1'[ValueB] ),
        FILTER (
            ALLSELECTED ( 'DataSource1' ),
            SEARCH ( MAX ( 'Data Mapping'[AttributeA] ), 'DataSource1'[AttributeA], 1, 0 )
                && 'DataSource1'[AttributeB] = MAX ( 'Data Mapping'[AttributeB] )
        )
    )
VAR ValueC =
    CALCULATE (
        SUM ( 'DataSource1'[ValueC] ),
        FILTER (
            ALLSELECTED ( 'DataSource1' ),
            SEARCH ( MAX ( 'Data Mapping'[AttributeA] ), 'DataSource1'[AttributeA], 1, 0 )
                && 'DataSource1'[AttributeB] = MAX ( 'Data Mapping'[AttributeB] )
        )
    )
VAR ValueD =
    CALCULATE (
        SUM ( 'DataSource2'[ValueD] ),
        FILTER (
            ALLSELECTED ( 'DataSource2' ),
            SEARCH ( MAX ( 'Data Mapping'[AttributeA] ), 'DataSource2'[AttributeA], 1, 0 )
                && 'DataSource2'[AttributeB] = MAX ( 'Data Mapping'[AttributeB] )
        )
    )
RETURN
    IF (
        MAX ( 'Data Mapping'[AttributeC] ) <> "Special",
        ValueA + ValueB + ValueC,
        ValueD
    )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more qui

 

patrickrw99's avatar
patrickrw99
Frequent Visitor
4 years ago

Hi, Thank you for your help, unfortunately, it still doesn't adhere to my needs. I think I also forgot to mention that I'm trying to display the data as a matrix classified by different values of AttributeB (In your case Based on Group A, Group B and Group C), and by doing so the numbers will be different as it will only take the Max Values of AttributeA and AttributeC that happens to have that specific AttributeB, which is not what I wanted to display. Please find the attached image that shows exactly what my problem is. I'm using the PBIX file you gave, just modifying the visual to highlight the problem. Thank you! 

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi patrickrw99 ,

     

    I create another measure, please try it and see if it works.

     

    SumMeasure = SUMX ( VALUES ( 'Data Mapping'[AttributeA] ), [Measure] )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.