Forum Discussion
Dax calc help
Hello All,
I want help in formulating DAX for my use case.
Sample data is below. I want to show/FILTER only those rows whose latest month (MAR) value is >0.
Creating a if statement on the value field >0 is applying on all the months, BUT, I want it to apply only on latest MAR month.
As such Name "F" should filter out per my result. How can i get this. Please help.
| Name | MAR | FEB | JAN |
| A | 18 | 14 | 16 |
| B | 21 | 22 | 13 |
| C | 11 | 7 | 16 |
| D | 19 | 22 | -12 |
| E | 10 | -7 | -11 |
| F | -11 | 10 | 16 |
| G | 1 | 4 | 7 |
Hi Anonymous,
According to your description, your table seems to contain multiple month fields with values and you are try to filter on them to hide the last month records that than less than zero?
If this is a case, I'd like to suggest you do unpivot columns on your month fields to convert them to attitude and value fields.
Then you can write a measure formula and use on 'visual level filter' to filter last month records.
Applying a measure filter in Power BI
measure = VAR list = ADDCOLUMNS ( VALUES ( Table[Attribute] ), "Date", DATEVALUE ( [Attribute] & "/1" ) ) VAR _lastMonth = MAXX ( list, [Date] ) VAR currValue = CALCULATE ( SUM ( Table[Value] ), FILTER ( ALLSELECTED ( Table ), [Attribute] = FORMAT ( _lastMonth, "MMM" ) ), VALUES ( Table[Name] ) ) RETURN IF ( SELECTEDVALUE ( Table[Attribute] ) = FORMAT ( _lastMonth, "MMM" ) && currValue > 0, "Y", "N" )Regards,
Xiaoxin Sheng
5 Replies
- Greg_Deckler
Community Champion
Anonymous - Is that really how your data looks? Thinking you should unpivot those month columns.
- amitchandak
Super User
Anonymous , this data should be unpivoted
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/an with year.
In this case, you can do is put a visual level filter of [MAR] >0
- Pragati11
Hi Anonymous ,
Consider following sample data:
Create a caluclated column as follows:
newCol = CALCULATE(SUM(DaxCalc[Mar]), DaxCalc[Mar] > 0)Move this to your table visual, you get following output:Thanks,Pragati Hi Anonymous,
According to your description, your table seems to contain multiple month fields with values and you are try to filter on them to hide the last month records that than less than zero?
If this is a case, I'd like to suggest you do unpivot columns on your month fields to convert them to attitude and value fields.
Then you can write a measure formula and use on 'visual level filter' to filter last month records.
Applying a measure filter in Power BI
measure = VAR list = ADDCOLUMNS ( VALUES ( Table[Attribute] ), "Date", DATEVALUE ( [Attribute] & "/1" ) ) VAR _lastMonth = MAXX ( list, [Date] ) VAR currValue = CALCULATE ( SUM ( Table[Value] ), FILTER ( ALLSELECTED ( Table ), [Attribute] = FORMAT ( _lastMonth, "MMM" ) ), VALUES ( Table[Name] ) ) RETURN IF ( SELECTEDVALUE ( Table[Attribute] ) = FORMAT ( _lastMonth, "MMM" ) && currValue > 0, "Y", "N" )Regards,
Xiaoxin Sheng
