Forum Discussion

kyrpav's avatar
kyrpav
Icon for Helper V rankHelper V
6 years ago

Find first non blank after ordering a filtered table

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 r...
Need Help
v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    kyrpav
    Icon for Helper V rankHelper V
    6 years ago

    Let me give you some more example

     

    codedateconditional_formatinggeneric_ordervalues
    12020-06-01 40015
    12020-06-01#eb331840025
    12020-06-01#dddddd40035
    12020-07-01#dddddd400110
    12020-07-01 400210
    12020-07-01#eb3318400310
    22020-06-01 50017
    22020-06-01 50027
    22020-06-01#dddddd50037
    22020-07-01#eb331850011
    22020-07-01 50021
    22020-07-01 50031

     

     
     
     
     

    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's avatar
      v-xicai
      Icon for Community Support rankCommunity Support
      6 years ago

      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.