DAX filter with condition

[amit_msmba]

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?

[amit_msmba]

I want to return as Table. Are you suggesting to write DAX for calculated table, instead of measure?

[FreemanZ · Accepted solution]

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:

[amit_msmba]

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?

[FreemanZ]

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"?