Forum Discussion

Jules___'s avatar
Jules___
Frequent Visitor
2 years ago
Solved

DAX code to create calculated column based on multiple criteria

Hi I am struggling with creating a calculated colum to show some data  - i need it to be a calculated colum rather than a measure as I want to use the results in a map with conditional formatting.

 

here is the issue  I have the following data

 

locationquestion categorydatescore
londonsafety1/6/235
parisdata 1/2/240
londonsafety 1/2/242
madridfinance 1/2/245
londonfinance 1/2/242
madriddata1/6/235
paris safety1/6/235
parissafety 1/2/245
madriddata1/6/232
londonfinance 1/2/240

 

I want to create in a new table the summed score for each location for a particular category but i only want to use the data from the most recent date.

 

so far i have 

actual = VAR _location = 'table2'[location]
VAR _score =SELECTEDVALUE('table1'[location ])
VAR result =
CALCULATE(SUM('table1'[Score]), FILTER('table 1',table1'[category]="safety"), ALL('table 1'),
'table 1'[location]=_location
)

RETURN
result)
 
I just cannot work out how to also include date as a variable
 
many thanks for having a look
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

    GROUPBY function (DAX) - DAX | Microsoft Learn

     

    TREATAS function - DAX | Microsoft Learn

     

    expected result table = 
VAR _condition =
    GROUPBY (
        data,
        data[location],
        data[question category],
        "@maxdate", MAXX ( CURRENTGROUP (), data[date] )
    )
VAR _t =
    CALCULATETABLE (
        data,
        TREATAS ( _condition, data[location], data[question category], data[date] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _t, data[location], data[question category], data[date] ),
        "@ScoreSum", CALCULATE ( SUM ( data[score] ) )
    )

     

     

2 Replies

  • Jules___'s avatar
    Jules___
    Frequent Visitor
    2 years ago

    amazing this worked perfectly - thank you so much for your help

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

    GROUPBY function (DAX) - DAX | Microsoft Learn

     

    TREATAS function - DAX | Microsoft Learn

     

    expected result table = 
VAR _condition =
    GROUPBY (
        data,
        data[location],
        data[question category],
        "@maxdate", MAXX ( CURRENTGROUP (), data[date] )
    )
VAR _t =
    CALCULATETABLE (
        data,
        TREATAS ( _condition, data[location], data[question category], data[date] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _t, data[location], data[question category], data[date] ),
        "@ScoreSum", CALCULATE ( SUM ( data[score] ) )
    )