How to list values which has data first from a distinct list

[Anonymous]

Hi there,

I have a chicklet slicer which has a list. (proivdes a distinct values) Some of it has corresponding data some dont. 

For example (the below report has same concept):

There are 50 states in US. However there could be data only for lets say 10 states. I would like my 10 states to surface first on the list. (Refer screenshot below)

2 tables

- State

- Data (joined based on StateID) 1 to many bidirectional relationship 

let me know if you are able to download the sample pbix

https://drive.google.com/file/d/1_0nT611GoJMAZ4PCyEMChENa_O78NqSW/view?usp=sharing

Thanks,
AJ

[mh2587]

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

i have made some changes with your pbix please proceed with same logic in file hope you will get it

[Anonymous]

Thank you for your time. I noticed when i click the benefits on the top, the left side list does not change.

The intent im trying to use * is because the chicklet does not allow to set background color of the chicklets to the ones which have data.

[mh2587]

If you select any of category the top visual will automatically change 

[Anonymous]

Is there a way to make the category visuals change when Departments are clicked as well?

Since users can click both ways.

[mh2587]

oh Sorry put the *category column from department in the left visual then it will work

[mh2587]

You need calculted table in which create two column "Expected List" and " Sorting array" and then sort the expected list column through sorted array that contain numerical values 1 to n records

[smpa01]

@Anonymous  upload the pbix in g/1 drive and share the link here

[Anonymous]

Thank you. I have posted the pbix as a link in the original post. Plz let me know if the link works.

[smpa01]

@Anonymous  you can do the following set up, it will only show the departments for which that measure has data.

[Anonymous]

Thank you. However we need all the categories. Listing out all categories is necessary since they get to know the categories and enter data (via other apps) accordingly.