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

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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      patrickrw99
      Frequent Visitor
      4 years ago

      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's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity 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