Convert Calculated Column to Measure, Need to use Row Context

[patrickrw99]

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 Mapping'[AttributeA],'DataSource1'[AttributeA],1,0) &&   'DataSource1'[AttributeB]=EARLIER('Data Mapping'[AttributeB])))
VAR ValueB=CALCULATE(SUM('DataSource1'[ValueB]),FILTER('DataSource1',SEARCH('Data Mapping'[AttributeA],'DataSource1'[AttributeA],1,0) &&   'DataSource1'[AttributeB]=EARLIER('Data Mapping'[AttributeB])))
VAR ValueC=CALCULATE(SUM('DataSource1'[ValueC]),FILTER('DataSource1',SEARCH('Data Mapping'[AttributeA],'DataSource1'[AttributeA],1,0) &&   'DataSource1'[AttributeB]=EARLIER('Data Mapping'[AttributeB])))
VAR ValueD=CALCULATE(SUM('DataSource2'[ValueD]),FILTER('DataSource2',SEARCH('Data Mapping'[AttributeA],'DataSource2'[AttributeA],1,0) &&   'DataSource2'[AttributeB]=EARLIER('Data Mapping'[AttributeB])))
return
if('Data Mapping'[AttributeC] <>"Special", ValueA+ValueB+ValueC,ValueD)

Basically this calculated column  has 2 main possible calculated outputs:
1. if AttributeC in a given record in 'Data Mapping' Table is NOT "Special", then  it will look for values in 'DataSource1' Table and conducts a sort of SUMIFS action based on the attribute A and Attribute B from 'Data Mapping' and matches the corresponding Attribute A and Attribute B that can also be found in 'DataSource1'. 'Data Mapping' and 'DataSource1' doesn't have any relationship since they are many-to-many. It returns 3 values per record from 'DataSource1', Value A,B and C where it will be added to become the final result
2. if AttributeC in a given record in 'Data Mapping' Table is "Special", then  it will do the same as No 1, but from a different data source table, which is 'DataSource2'. It will only return 1 value, which is Value D.

Please help me to turn this calculated column into a working measure function, as I have not found the correct method to do so. My main problem I think is trying to get the row context of each individual record from the 'Data Mapping' Table, which means I can't get the correct numbers. Any help is appreciated, thanks!

[amitchandak]

@patrickrw99 , Try like

Calculated Column =
VAR ValueA=CALCULATE(SUM('DataSource1'[ValueA]),FILTER(allselected('DataSource1'),SEARCH('Data Mapping'[AttributeA],'DataSource1'[AttributeA],1,0) && 'DataSource1'[AttributeB]=MAX('Data Mapping'[AttributeB])))
VAR ValueB=CALCULATE(SUM('DataSource1'[ValueB]),FILTER(allselected('DataSource1'),SEARCH('Data Mapping'[AttributeA],'DataSource1'[AttributeA],1,0) && 'DataSource1'[AttributeB]=MAX('Data Mapping'[AttributeB])))
VAR ValueC=CALCULATE(SUM('DataSource1'[ValueC]),FILTER(allselected('DataSource1'),SEARCH('Data Mapping'[AttributeA],'DataSource1'[AttributeA],1,0) && 'DataSource1'[AttributeB]=MAX('Data Mapping'[AttributeB])))
VAR ValueD=CALCULATE(SUM('DataSource2'[ValueD]),FILTER(allselected('DataSource2'),SEARCH('Data Mapping'[AttributeA],'DataSource2'[AttributeA],1,0) && 'DataSource2'[AttributeB]=MAX('Data Mapping'[AttributeB])))
return
if('Data Mapping'[AttributeC] <>"Special", ValueA+ValueB+ValueC,ValueD)

[patrickrw99]

Hi, I've tried your solution but still no luck. The numbers resulting from this is still different from the calculated column. I would like to also add that the conditionals using Attribute C seems to not be working when using measure as it's not sjowing any results for items with Special for the Attribute C

[v-kkf-msft]

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]

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 · Accepted solution]

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.