topic Re: Slicer with different output in DAX Commands and Tips

Slicer with different output

ArtemisRaeWJ — Wed, 29 Jan 2025 13:53:22 GMT

Hi community, I need guidance on this particular request.
I have a PBI port-to-port dashboard. The objective is to show types of port services & lead time from point A to point B in our existing trade in the database. Current roadblock is user want to show other output under the same group name & it needs to be dynamic. Here a sample data table:-

Port_Code	Port_Name	Group_Name
ADL	Adelaide	Southern Australia
HBA	Hobart	Southern Australia
MEL	Melbourne	Southern Australia
SYD	Sydney	Southern Australia
TSV	Townsville	Southern Australia

Example scenario :-
If I select "Adelaide" in the "Port of Loading" Slicer & "Laem Chabang" in the "Port of Discharge" slicer, in the table output must show the alternative ports in "Port of Loading" that is under "Southern Australia" group name which is Adelaide, Melbourne & Sydney.
User also requested to do away of including group name in the hierarchy drop down slicers.
Slicer Layout

Table output

I have tried to adapt one of the DAX from YT video by SQLBI Show last 6 months based on user single slicer selection that might work for this requirement, but I got a blank output

POL Outbound Select = VAR SelectedPOL = SELECTEDVALUE('data_sample'[Port_of_Loading]) VAR ReferenceGroupNameBySelectedPortName = IF( NOT(ISBLANK(SelectedPOL)), CALCULATE( FIRSTNONBLANK('data_sample'[Port_of_Loading_Group_Name], 1), 'data_sample'[Port_of_Loading] = SelectedPOL ), BLANK() ) VAR Result = CALCULATE( SELECTEDMEASURE(), REMOVEFILTERS('data_sample'[Port_of_Loading_Group_Name]), REMOVEFILTERS('alt_data_sample'[Port_of_Loading]), // from dim table of 'data_sample' FILTER( 'data_sample', 'data_sample'[Port_of_Loading_Group_Name] ), USERELATIONSHIP('data_sample'[Port_Code], 'alt_data_sample'[Port_Code]) ) RETURN Result

Sample PBIX file

Hope to get some help on this matter here or other workaround & let me know if you need more infomation. Thank you in advance

Re: Slicer with different output

Anonymous — Wed, 29 Jan 2025 06:41:21 GMT

Hi ArtemisRaeWJ ,

Thanks for reaching out to us.

Not very clear about your requirement. Why did you choose "Laem Chabang" to return to "Southern Australia", and how are the two related in the data model?

Please describe the relationship between them in more detail.

Best Regards,
Stephen Tao

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

Re: Slicer with different output

ArtemisRaeWJ — Wed, 29 Jan 2025 13:44:16 GMT

Hi Anonymous,
This requirement is related to Port-to-port dashboard which consist of types of port services & lead time. The earlier given example is actually one of the seaport from point A to point B in the existing trade from the database. There are long list of other port names

Because the user wants to "force" showing other ports when selected the port name that is under the same category (which is the group name) instead of having a hierarchy dropdown slicer the group name which the user rejected

I have included a sample PBIX for testing

Re: Slicer with different output

ArtemisRaeWJ — Mon, 03 Feb 2025 11:52:43 GMT

Hi all,

I have found the solution for this case.

CountRows PortGroups = 
VAR _selectedPortGroup = SELECTEDVALUE(helpTable[Port Group])

RETURN

CALCULATE(COUNTROWS(originalTable), originalTable[Port Group] = _selectedPortGroup)

Credits to Reddit user: frithjof_v. Click here for more info in the steps

Re: Slicer with different output

Anonymous — Tue, 04 Feb 2025 05:42:51 GMT

Hi ArtemisRaeWJ ,

Really glad to hear that your problem has been solved.

If possible, please accept it as a solution. More people will benefit from your reply.

Best Regards,
Stephen Tao