Forum Discussion

curtismob's avatar
curtismob
Helper IV
4 years ago

Using the First Value Of a Group

Hello,

Below is what I am trying to accomplish.

  • Use the cost for the first vendor in the grouping, where the "As Of" date selected falls w/in the Effective/Expiration range.
  • Grouping is by Category/Subcategory/Cost Code
  • Needs to work with the slicer selections
  • Tried RANKX and TOPN, neither with the desired results.

I have the pbix for the example below, but not sure how to post it.

 

 

Thanks in advance,

curtismob 

 

Need Help

8 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    4 years ago

    Hi curtismob 

     

    Please upload pbix file in google drive, make any one can access it and share the link here

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        4 years ago

        Hi curtismob ,

         

        Try to create a measure like below and apply it to visual level filter:

        Measure 2 =
var rank_ =
RANKX (
ALLEXCEPT( Cost,Cost[CostCode] ),
[Measure Cost AsOf]
)
var min_vendor = CALCULATE(MIN(Cost[VendorNo]),FILTER(ALLSELECTED(Cost),Cost[CostCode]=MAX(Cost[CostCode])))
return IF(rank_=1&&MAX(Cost[VendorNo])=min_vendor,1,0)

         

         

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

  • curtismob's avatar
    curtismob
    Helper IV
    4 years ago

    V-lianl-msft,

    I tried your suggestion, thank you!!  It is close but doesn't work when the date selected is changed to include the older effective/expiration date range.  The older date range cost is included in the first screen print, but see the second screen print and what happens when I add Measure 2 to the grid.

     

    Before adding Measure 2 to the grid:

     

    After adding Measure 2 to the grid:

     

     

     

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support
      4 years ago

      Hi ,

       

      Please apply it to visual level filter not put it into table visual.

       


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

      • curtismob's avatar
        curtismob
        Helper IV
        4 years ago

        Hi, I did have the visual filter set to 1 for Measure 2, but unfortunately it still doesn't work when the as of date is changed from 2/1/2022 to 1/12/2022.  The cost for cost code 1234 should change from $250 to $150 instead.