Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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:
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:
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?
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"?
User | Count |
---|---|
46 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |