Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I would like to create a DAX formula with a IF statement.
my formula would be :
IF('DATA'[Work Stream ] ="WS 1.1";SUM('DATA'[KPI 2 Monthly Actual]);
IF('DATA'[Work Stream ] ="WS 2.1";SUM('DATA'[KPI 2 Monthly Actual]);
IF('DATA'[Work Stream ] ="WS 2.2";AVERAGE('DATA'[KPI 2 Monthly Actual]);
IF('DATA'[Work Stream ] ="WS 3.1";SUM('DATA'[KPI 2 Monthly Actual]);
IF('DATA'[Work Stream ] ="WS 3.4";SUM('DATA'[KPI 2 Monthly Actual]);
IF('DATA'[Work Stream ] ="WS 3.5";AVERAGE('DATA'[KPI 2 Monthly Actual])
Maybe it is possible with a look up Table ? (1st field : Work Stream, 2nd field : aggregation type)
anyway, even without look up Table it can be great !
Thank you for your Quick answer!
Solved! Go to Solution.
a lady from the MS support gave me a solution that seems ok :
---
"
Hi Augustin,
Here are the measures that you will need:
In will need to substitute what is in orange with your dimensions.
And here are some interesting documentation:
https://msdn.microsoft.com/en-us/library/ee634396.aspx;
http://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/;
"
---
Try This if u want ...
CalculatedColumn= SWITCH(
TRUE(),
TableName[ColumnName] = "A",
Value(123),
TableName[ColumnName] = "B",
Value(124),
TableName[ColumnName] = "C",
Value(125),
TableName[ColumnName] = "D",
Value(126),
TableName[ColumnName] = "E",
Value(127),
TableName[ColumnName] = "F",
Value(128),
TableName[ColumnName] = "G",
Value(129),
TableName[ColumnName] = "H",
Value(130),
TableName[ColumnName] = "I",
Value(131),
TableName[ColumnName] = "J",
Value(132),
TableName[ColumnName] = "K",
Value(134),
TableName[ColumnName]= "L",
Value(135),
TableName[ColumnName] = "M",
Value(136),
-1)
I'm relatively new to PowerBI and DAX and I'm having a problem with a similar issue (not as complicated I think). I need to use the volume if it is current YTD, Actuals and ITA otherwise 0.
These are the two DAX statements I have tried:
_CurrentYearITA = IF('AMER DBP Retail Bookings'[DTF_Current_ITA_YTD] = "Y"||'AMER DBP Retail Bookings'[PL_PlanCode] = "ACTUALS"|| 'AMER DBP Retail Bookings'[CO_Company] = "ITA";'AMER DBP Retail Bookings'[_Volume];0)
and
_CurrentYearITA = IF(AND('AMER DBP Retail Bookings'[DTF_Current_ITA_YTD] = "Y",'AMER DBP Retail Bookings'[PL_PlanCode] = "ACTUALS",'AMER DBP Retail Bookings'[CO_Company] = "ITA"),'AMER DBP Retail Bookings'[_Volume],0)
The first one gives a bad syntax error starting with the semi-colon after "ITA" and the second one says too many arguments for AND function.
Any assistance would be appreciated.
Hi @augustindelaf,
Please try to create a measure like below to see if it meet your requirement:
Measure = SWITCH(TRUE(),MAX('DATA(Update KPIs)'[Work Stream ])="WS 1.1" || MAX('DATA(Update KPIs)'[Work Stream ])="WS2.1" || MAX('DATA(Update KPIs)'[Work Stream ])="WS 3.1" || MAX('DATA(Update KPIs)'[Work Stream ])="WS 3.4",SUM('DATA(Update KPIs)'[KPI 2 Monthly Actual]),
MAX('DATA(Update KPIs)'[Work Stream ])="WS 2.2" || MAX('DATA(Update KPIs)'[Work Stream ])="WS 3.5",AVERAGE('DATA(Update KPIs)'[KPI 2 Monthly Actual]))
Best Regards,
Qiuyun Yu
a lady from the MS support gave me a solution that seems ok :
---
"
Hi Augustin,
Here are the measures that you will need:
In will need to substitute what is in orange with your dimensions.
And here are some interesting documentation:
https://msdn.microsoft.com/en-us/library/ee634396.aspx;
http://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/;
"
---
Hi @augustindelaf,
I believe that the Switch function will work much better than the IF, try this.
SWITCH( TRUE(); 'DATA'[Work Stream ] = "WS 1.1"; SUM('DATA'[KPI 2 Monthly Actual]); 'DATA'[Work Stream ] ="WS 2.1"; SUM('DATA'[KPI 2 Monthly Actual]); 'DATA'[Work Stream ] ="WS 2.2"; AVERAGE('DATA'[KPI 2 Monthly Actual]); 'DATA'[Work Stream ] ="WS 3.1"; SUM('DATA'[KPI 2 Monthly Actual]); 'DATA'[Work Stream ] ="WS 3.4"; SUM('DATA'[KPI 2 Monthly Actual]); 'DATA'[Work Stream ] ="WS 3.5"; AVERAGE('DATA'[KPI 2 Monthly Actual]); 0)
You can change the final 0 by the default value you want.
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsFYI :
it must be row by row operation,
then it has to be a calculated column, and not a measure.
thank you
Hi @augustindelaf,
Try below formula
Formula = IF(AND('DATA'[Work Stream ] ="WS 1.1", 'DATA'[Work Stream ] ="WS 2.1"),SUM('DATA'[KPI 2 Monthly Actual]),
IF(AND('DATA'[Work Stream ] ="WS 3.1", 'DATA'[Work Stream ] ="WS 3.4"),SUM('DATA'[KPI 2 Monthly Actual]),
IF(AND('DATA'[Work Stream ] ="WS 2.2", 'DATA'[Work Stream ] ="WS 3.5"),AVERAGE('DATA'[KPI 2 Monthly Actual]),0)))
Hope it work
Thanks,
Anupam
Hi,
@anupampandey, @MFelix, thanks for your solution.
I will keep the SWITCH solution, which to me is the easiest one.
just one problem : it does not act within the current filter context, but doing sums or averages without any filtering.
and i have some filters applied (owner, action ID, Region...) but the results appear as if filters were not applied.
can you tell me how to do it to the current filter context?
it is a calculated column, not a measure, btw
thanks !
Hi @augustindelaf,
How did you set filters (owner, action ID, Region...)? If you use the calculated column to return results, filters will not affect the value in this column. I think you might need to create a measure which can be filtered.
Please share the sample table about 'DATA'[Work Stream ], 'DATA'[KPI 2 Monthly Actual], 'DATA'[KPI 2 Monthly Actual] and owner, action ID, Region. And show us which filtered results you want, so that we can try to create a measure to achieve your requirements.
By the way, regarding measures and calculated columns, please take a look at below:
Tutorial: Create calculated columns in Power BI Desktop
Best Regards,
Qiuyun Yu
hI @v-qiuyu-msft,
Thank you for answering me and proposing me to send a sample.
I don't really know Measures and how for values to act in the current filter context.
link to the file is here :
https://filetea.me/n3wVarFBmlySNqeM61cTuQJrg
please go to the 1st Tab (Monthly), you will see filters on the Top. (blue ribbon)
according to some values I need some fields to be in average or sums.
thanks !
if 1st link doesn't work, I put the file on google drive :
https://drive.google.com/file/d/0B0os9aXobQDBLWJhQkM4dzg3alk/view?usp=sharing
Maybe I don't understand enough the difference between a Measure and a Calc Column.
But my question is :
-how to make that sum & average work IN the current filter context ?
if a measure can solve that, then I will do a measure.
thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |