Forum Discussion
How to covert excel formula into Dax
Hello All,
I have table as below image.
Table1
I have written formula to get the plansum value for everymonth.
If month value is not available then it should comapre with the previuos month value.
Formula:-
J7
| =IF(H7=I7,"",IF(H7/I7>I7,UNICHAR(8679),UNICHAR(8681))) |
H7 I7
| {=INDEX(Table1[PlanSum],LARGE((ABS(Table1[PlanSum])>0)*ROW(Table1[PlanSum]),1)-(CELL("Row",E2)-1))} |
| {=INDEX(Table1[PlanPrevMon],LARGE((ABS(Table1[PlanPrevMon])>0)*ROW(Table1[PlanPrevMon]),2)-(CELL("Row",E2)-1))} |
So how can i change these formuals into dax.
Any suggestions.
Mohan V
Well I think i found the solution Anonymous.
I have gone through the link that you have provided and after lots of mess..
I think i got the solution.
Measure is
LastNonBlank Plan = var LastNonBlankDate=CALCULATE(MAX('Month Year'[Date]),FILTER(ALL('Month Year'),'Month Year'[Date]<=MAX('Month Year'[Date]) && [PlanSum]<>0)) return CALCULATE([PlanSum], FILTER(ALL('Month Year'),'Month Year'[Date]=LastNonBlankDate) )Output is:-
As i expected, i need to show only the last non blank value in cards i think it is giving me perfect output.
Correct me if i am wrong.
And thanks Anonymous. you have spent a lots of time and you have given me lots of inputs to explore myself.
Keep in touch.
17 Replies
- dkay84_PowerBI
Microsoft Employee
Why do you need to change to DAX? Your example doesn't indicate that you are working with a data model and DAX isn't really designed for referencing individual cells like your excel formulas. I think you are approaching this the wrong way. Instead, start with the goal you are trying to achieve and look for the best method to do this within Power BI. In some cases there will be some Excel -> Dax converting you might do, but here it looks like you want to do something that is suited to a completely different approach.
Are you able to write in a paragraph what you are attempting to achieve?Thanks for the replay Anonymous , dkay84_PowerBI.
I was just trying whether it works with excel or not.
But as you guys are saying that it is not suited with excel then i will go with the complete powerbi approach.
Actually
I have created the custom kpi using below measure.
PlanArrows = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))
And it works fine for me for perticular month which i choose from chicklet slicer of months.
But here i dont want user to choose the month values from slicer.
I would like to show the card values as
if current month value is not availble then it should compare with the previous month value automatically.
like
In above image Apr-2017 value is not available then it should compare with the Mar-2017 value.
If Mar-2017 value also not available then the previous mont value.Keep on goes
Any suggestions.
Mohan V
The idea is possible, but will require an overhaul. Slicer work by limiting the data your dax expressions see. If you select April, March data is essentually hidden.
If you to do what you are talking about, you would need to set up a date table that is not linked to the rest of your data tables. This table would hold current month and previous month. These dates are linked to your slicer only, then your DAX expressions will need to run their own CALCULATE functions to filter the data dynamically, trying to mimic what the slicer was previously doing. That Calculate function would need to be run by looking up the Date table (which your slicer has reduced) looking for a current month or previous month value.
Your CALCULATE functions would get this months value and previous months value. You can then have a 3rd measure which is an "If 'Measure 1' > 0 then 'Measure 1' else 'Measure 2'" style measure.
