Using the First Value Of a Group

[curtismob]

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 

[curtismob]

@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]

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]

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.

[curtismob]

Link for the pbix.

https://drive.google.com/file/d/19svDZp3xEVu44yePjN6rCWKIDbdXnhly/view?usp=sharing 

[PijushRoy]

Hi @curtismob 

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

[curtismob]

Hi @PijushRoy,

I have shared the Google Drive link.

Thank you,

@curtismob 

[V-lianl-msft]

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]

Hi @V-lianl-msft,

Thank you for the response, I'm not sure why I wasn't notified you reponded.  I will give this a try and update this thread.

@curtismob