topic Re: DAX TO GET THE NEXT 3 SHIPMENTS in DAX Commands and Tips

DAX TO GET THE NEXT 3 SHIPMENTS

HMSF — Wed, 15 May 2024 22:24:54 GMT

Hi everyone,

I am new Power BI user. I have a table with the vessel name and the volume to be discharged and I want to create a visual that shows me the next three shipments to be discharged depending on the slicer range. I would like to know why kind of DAX formula I can use. I have been trying to use the Top N filter but it doesn't woprk so I am not sure what I am doing wrong.

Re: DAX TO GET THE NEXT 3 SHIPMENTS

Anonymous — Thu, 16 May 2024 02:26:16 GMT

Hi HMSF ,

Which field you applied on the slicer? What's your expected result? Could you please explain the backend logic base on the provided sample data? It would be helpful to get the solution. Thank you.

Best Regards

Re: DAX TO GET THE NEXT 3 SHIPMENTS

HMSF — Thu, 16 May 2024 15:38:59 GMT

The field that I applied for the slicer is the date. The result that I want is that when I choose a date range (slicer) I can see the next 3 vessels name with the material and the volume.

Re: DAX TO GET THE NEXT 3 SHIPMENTS

Anonymous — Wed, 22 May 2024 07:09:42 GMT

Hi HMSF ,

Thanks for your reply. I'm still not clear about your requirement. As you said, you applied a date field on the slicer. Base on your sample data, if select the date from 5/1/2024 to 5/22/2024, then it will display 3 rows with red square in the below screenshot?

Best Regards

Re: DAX TO GET THE NEXT 3 SHIPMENTS

HMSF — Wed, 22 May 2024 15:00:02 GMT

I want that when I select a date range in the slicer I can see the current shipments and the next three shipments

Re: DAX TO GET THE NEXT 3 SHIPMENTS

Anonymous — Tue, 28 May 2024 09:49:04 GMT

Hi HMSF ,

You can follow the steps below to get it:

1. Create a date dimension table(Do not create any relationship with your fact table)

2. Create a slicer and apply the date field of the above date dimension table on it

3. Create a measure as below

Flag = var _mindate=min('Date'[Date]) var _maxdate=max('Date'[Date]) var _seldate=selectedvalue{'Facttable'[Date]) return if(_seldate>=_mindate&&_seldate<=_maxdate,1,0)

4. Create a table visual and apply the visual-level filter with the condition (Flag is 1)

Best Regards