topic DAX code to create calculated column based on multiple criteria in DAX Commands and Tips

DAX code to create calculated column based on multiple criteria

Jules___ — Mon, 26 Feb 2024 16:52:38 GMT

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

location	question category	date	score
london	safety	1/6/23	5
paris	data	1/2/24	0
london	safety	1/2/24	2
madrid	finance	1/2/24	5
london	finance	1/2/24	2
madrid	data	1/6/23	5
paris	safety	1/6/23	5
paris	safety	1/2/24	5
madrid	data	1/6/23	2
london	finance	1/2/24	0

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

Re: DAX code to create calculated column based on multiple criteria

Jihwan_Kim — Mon, 26 Feb 2024 18:03:50 GMT

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

Re: DAX code to create calculated column based on multiple criteria

Jules___ — Wed, 28 Feb 2024 12:24:12 GMT

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