Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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
Hi @Anonymous ,
Consider following sample data:
Create a caluclated column as follows:
@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
@Anonymous - Is that really how your data looks? Thinking you should unpivot those month columns.
HI @Anonymous ,
Create a visual filter
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |