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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

show those name in filter which has value

Dear All,

 

I have a table having field city name, now in another table some city doesn't have any stores so I want to give filter where only shows only those name which has stores, not those which doesn't have city but there is one thing that in future they can open/map some stores in that cities so those should be coming when it has value..

please help me how can i calculate this??

 

rgrds,

arif

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Here are the steps you can follow:

1. Create measure.

Measure_Count = COUNT('Fact Table'[Region Code])

2. Place [Measure_Count] in the Filter that forms a slice with [Region Code], and set is not blank.

vyangliumsft_3-1634696932418.png

3. Result:

vyangliumsft_1-1634696857535.png

After adding a piece of data, AJM also has data, which will also be displayed normally in the slicer

vyangliumsft_2-1634696857536.png

 

Best Regards,

Liu Yang

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

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Here are the steps you can follow:

1. Create measure.

Measure_Count = COUNT('Fact Table'[Region Code])

2. Place [Measure_Count] in the Filter that forms a slice with [Region Code], and set is not blank.

vyangliumsft_3-1634696932418.png

3. Result:

vyangliumsft_1-1634696857535.png

After adding a piece of data, AJM also has data, which will also be displayed normally in the slicer

vyangliumsft_2-1634696857536.png

 

Best Regards,

Liu Yang

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

Tahreem24
Super User
Super User

@Anonymous Tried to replica your issue at my end.

Capture.JPG

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

This is not what I am asking my friend,

 

 

i am asking that if i put the city filter it will show all the cities including those which has no stores in another tables

if I unselect the way you mentioned but let's say tomorrow if they open store then that will open then it will not show in the filter so i have to make a calculated column which should work dynamically.

 

rgrds,

arif

 

Tahreem24
Super User
Super User

@Anonymous Create Calculated column like below:

CALCULATED COLUMN = If(Table[City] = Blank(),1,0)
Then Take your City field in the Slicer and in Visual Filter of this Slicer just use the above new column and filter to 0. So it will show up only available city in your slicer.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

Thank you for reply my scenario is

 

City Table

Region CodeRegion Name
DXBDubai
ABDAbu Dhabi
AJMAjman
RSKRas-Al-Khaima
SHJSharjah
UMQUmm-Quwayn
FJHFujairah

 

FACT Table

 

Region CodeStore IDStore Name
DXBSt001ABC Plaza
DXBSt002BCD Plaza
DXBSt003CBD Plaza 
ABDSt004DEF Plaza
ABDSt005EFJ Plaza
ABDSt006FJK Plaza

 

 

now i have two tables

I only want those city names in my fillter which has stores but it should work dynamically, let's say tomorrow they open any store in different region then it should be coming there..

 

Please support

rgrds

arif

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.