Forum Discussion
DAX filter with condition
Hi Friends, I need a help writting this measure. Here is a sample data.
Plan Year Amt
Plan 1 2019 30
Plan 1 2020 40
Plan 1 2021 15
Plan 2 2015 20
Plan 2 2018 20
Plan 2 2020 10
If any Plan has Amt > 35, return all Years for that plan. In above sample, it should return Year 2019, 2020, 2021 as Plan 1 has Amt > 35. What should be the DAX for this Measure?
hi amit_msmba
try to plot a card visual with the measure like this:
YearList = VAR _plan = MINX( FILTER(TableName, TableName[Amt]>35), TableName[Plan] ) VAR _year = CALCULATETABLE( VALUES(TableName[Year]), TableName[Plan] = _plan ) RETURN CONCATENATEX( _year, TableName[Year], ", " )i tried and it worked like this:
4 Replies
FreemanZSuper User
hi amit_msmba
what you want to return is a list/table, it is not good for a measure directly. what are you going to do with the needed "measure"?
I want to return as Table. Are you suggesting to write DAX for calculated table, instead of measure?
- FreemanZ
hi amit_msmba
try to plot a card visual with the measure like this:
YearList = VAR _plan = MINX( FILTER(TableName, TableName[Amt]>35), TableName[Plan] ) VAR _year = CALCULATETABLE( VALUES(TableName[Year]), TableName[Plan] = _plan ) RETURN CONCATENATEX( _year, TableName[Year], ", " )i tried and it worked like this:
Hello FreemanZ,
Thanks for providing DAX for YearList. Instead of concatenated Years, I want it to return me 'Plan 1' with all the columns, as Plan 1 year 2020 meet the condition Amt > 35. The output should be something linke this, How to do that?
