Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
abartozzi
Frequent Visitor

Lowest Value Name

Hi community, 

 

Have tried some ideas but didn't work out. 

I have this pivot table:

 

Cattura.PNG

 

So as you can see, for every date there are multiple entries depending on the OTA. What I also have (not shown here) is a minimum column that locate the min value from the OTAs (Expedia, Findhotel, Edreams, etc) columns. What I need to specify is where that value comes from, in other words I need a column next to the min one that tells me where to find that price. 

 

Would it be this possible?

 

thanks 😄

1 ACCEPTED SOLUTION

Hi @abartozzi,

 

Depending on your data structure, calculating the field name corresponding to the maximum value in different fields is not supported, perhaps you can change the data structure to unpivot 'AVES', 'Bookling.com', 'Agoda.com', etc. into one column as follows.

vxulinmstf_0-1629787981590.png

vxulinmstf_1-1629788000471.png

Then try measure as:

 

Measure 2 = 
IF(
    [Measure]=MAXX(FILTER(ALL('test table'),'test table'[Category]=MAX('test table'[Category])),[Measure]),
    MAX('test table'[Subcategory]),
    BLANK()
)

 

I added this into the demo, please try it.

 

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

View solution in original post

7 REPLIES 7
v-xulin-mstf
Community Support
Community Support

Hi @abartozzi

 

If I understand correctly, do you want the lowest value corresponding to Expedia, Findhotel, Edreams, or some other field?
Maybe you can create a measure get it, could you provide you pbix after removing sensitive information?

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

Hi @v-xulin-mstf 

 

imagine another table where you have a "rate" column where it shows the min value of all columns (Expedia, Findhotels, etc) and another column next to it called "OTA" that shows the website selling it 

 

E.g in the chart i gave you Sunday Aug 22nd you have Maison Fleurie Record where Edreams is the cheapest value hence the measure should do:

 

HotelRateOTA
Maison Fleurie132Edreams

 

Where the table is a pivot (like the one i gave) where colums are on a first stage the date and following the example i gave you. In detail

 

  • the hotel: comes straight from the database
  • Rate: is a calculated column that picks the min of other columns (rates of: expedia, findhotels, etc)
  • OTA: is the measure that explicts the name of the min column of Rate 

hope it clarifies

 

 

Hi @abartozzi,

 

Try measure as:

Measure=
if(
   [Rate]=Minx(filter(all'table','table'[hotel]=max('table'[hotel])),[Rate]),
   max('table'[OTAs]),
   blank()
)

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

Hi @v-xulin-mstf 

 

I gave it a crack but didn't work out. I enclosed a copy of the report here

https://drive.google.com/file/d/1SZ6yfXjlU8sKOSe-FKHm7ZTgQaqvwutT/view?usp=sharing

 

there is no much sensitive information so you can play around. have a look and let me know

Hi @abartozzi,

 

Depending on your data structure, calculating the field name corresponding to the maximum value in different fields is not supported, perhaps you can change the data structure to unpivot 'AVES', 'Bookling.com', 'Agoda.com', etc. into one column as follows.

vxulinmstf_0-1629787981590.png

vxulinmstf_1-1629788000471.png

Then try measure as:

 

Measure 2 = 
IF(
    [Measure]=MAXX(FILTER(ALL('test table'),'test table'[Category]=MAX('test table'[Category])),[Measure]),
    MAX('test table'[Subcategory]),
    BLANK()
)

 

I added this into the demo, please try it.

 

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

amitchandak
Super User
Super User

@abartozzi , Based on what I got.

Remove OTA from column. Create this measure on you measure in matrix column

 

calculate(Min(Table[OTA]), ,TOPN( 1,allselected(Table[OTA]),[Measure],DESC), values(Table[OTA])))

 

and use this one

Hi @amitchandak 

 

just to clarify, OTA is not a column as in Expedia, Findhotels, etc. are OTAs so what i have to try to come up with is the name of the lowest value among all of these (there are more XD). So going back to your measure, shall I list all the involved columns (Expedia, Findhotels, etc) to imply the list of column to check?

 

thanks for your help

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors