Find first non blank after ordering a filtered table

[kyrpav]

I have a table that has one column with hex color code for conditional formating. So in table i want to use a measure that for a filtered part of the table (by a matrix) i can get the firstnonblank record.

code	date	conditional_formating	generic_order	values
1	2020/06/01		4001	5
1	2020/06/01	#eb3318	4002	5
1	2020/06/01	#dddddd	4003	5
1	2020/07/01	#dddddd	4001	10
1	2020/07/01		4002	10
1	2020/07/07	#eb3318	4003	10

Data are like this so my matrics i am selecting on

• row header: code ,
• in column i have: dates
• and in data i have: values

So based on the generic order i want to select the first not null value for each month for each code.

How can i do it cause classic call of firstnonblank(field,1) is not working

[v-xicai]

Hi @kyrpav ,

You may create measure like DAX below.

Measure1 =
CALCULATE (
    FIRSTNONBLANK ( 'Table1'[Payment Method], 1 ),
    FILTER (
        ALLSELECTED ( 'Table1' ),
        'Table1'[code] = MAX ( 'Table1'[code] )
            && YEAR ( 'Table1'[Date] ) = YEAR ( MAX ( 'Table1'[Date] ) )
            && MONTH ( 'Table1'[Date] ) = MONTH ( MAX ( 'Table1'[Date] ) )
    )
)

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kyrpav]

Let me give you some more example

code	date	conditional_formating	generic_order	values
1	2020-06-01		4001	5
1	2020-06-01	#eb3318	4002	5
1	2020-06-01	#dddddd	4003	5
1	2020-07-01	#dddddd	4001	10
1	2020-07-01		4002	10
1	2020-07-01	#eb3318	4003	10
2	2020-06-01		5001	7
2	2020-06-01		5002	7
2	2020-06-01	#dddddd	5003	7
2	2020-07-01	#eb3318	5001	1
2	2020-07-01		5002	1
2	2020-07-01		5003	1

So for code 1, generic order of 4002 have to be seleced with red color #eb3318 for june while for july #dddddd .

For code 3 for june i have to get color for generic order 5003 and for july color for generic order 5001.

[v-xicai]

Hi @kyrpav ,

You may create measure like DAX below.

conditional_formating_New =
VAR _lastnoblankorder =
    CALCULATE (
        MAX ( Table1[generic_order] ),
        FILTER (
            ALL ( Table1 ),
            Table1[code] = MAX ( Table1[code] )
                && Table1[date] = MAX ( Table1[date] )
                && Table1[generic_order] <= MAX ( Table1[generic_order] )
                && Table1[conditional_formating] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( Table1[conditional_formating] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[code], Table1[date] ),
            Table1[generic_order] = _lastnoblankorder
        )
    )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kyrpav , Create a measure like this and return the hexcode in place of color

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[kyrpav]

what is comodity why sum is needed ....? i do not want to know any value i want ot select this conditional formating measure in conditional formating of values for background color