Dax calc help

[Anonymous]

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

[Anonymous · Accepted 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.

Unpivot columns (Power Query) 

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

[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

[amitchandak]

@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

[Greg_Deckler]

@Anonymous - Is that really how your data looks? Thinking you should unpivot those month columns.

[harshnathani]

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)