Forum Discussion
How to write DAX by selecting only specific values in the rows
Hello all,
I'm trying to solve a problem by writing DAX.
I have 3 Columns in a table and the source is SSAS Datamodel.
1) Year
2) Measure
3) Cost
Sample data:
My expected Output should be in below format:
| YEAR | ||||||||||||
| Measure | Jul-21 | Aug-21 | Sep-21 | Oct-21 | Nov-21 | Dec-21 | Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 |
| Actual | 678 | 544 | 645 | 700 | 666 | 689 | 701 | |||||
| Forecast | 806 | 678 | 987 | 909 | 840 | 678 | 567 | 678 | 789 | 744 | 711 | 700 |
For Actual , it's straight forward as it coming from one measure, but for forecast, it keeps changing every month but the measure names will remain same.
How can we write DAX to get forecast from the above data as an example?
Thanks in Advance
Dee
Anonymous , I am trying to get the first forecast, In that will take based on the order of forecast
calculate(firstnonblankvalue(Table[Forecast], [Measure]), filter(Allselected(Table[Forecast]) , Table[Forecast] = max(Table[Forecast]) ) )
Also in the matrix, you have to use show on the row
refer
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak
3 Replies
- amitchandak
Super User
Anonymous , Create an order for Forecast
then you can have measure like
calculate(firstnonblankvalue(Table[Forecast], [Measure]), filter(Allselected(Table[Forecast]) , Table[Forecast] = max(Table[Forecast]) ) )
Hi amitchandak ,
Thanks for the response, that was very quick.
Sorry, I didn't understand.
Could you please explain in more detail.
Thanks
Dee
- amitchandak
Anonymous , I am trying to get the first forecast, In that will take based on the order of forecast
calculate(firstnonblankvalue(Table[Forecast], [Measure]), filter(Allselected(Table[Forecast]) , Table[Forecast] = max(Table[Forecast]) ) )
Also in the matrix, you have to use show on the row
refer
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak