Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amit_msmba
New Member

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?

1 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:

FreemanZ_0-1671171299334.png

View solution in original post

4 REPLIES 4
amit_msmba
New Member

I want to return as Table. Are you suggesting to write DAX for calculated table, instead of 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:

FreemanZ_0-1671171299334.png

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?

amit_msmba_0-1671464267584.png

 

 

FreemanZ
Super User
Super 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"?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors