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
singhv2
Helper I
Helper I

Find Occurrence of an Item in a column with respect to the value in another column.

Hello,

 

I am stuck in a situation, while working in "Direct Query" mode.

 

I am working to visualize the inventory details across our warehouses(Business Units), in POWER BI.

 

While doing so, I was planning to include a slicer, which can show the occurence of an "ITEM" in more than or equal to 2 warehouses. So that, if I select 2, it can show the availability at the locations.

 

Pivot is not helping me out as the data is huge, and it takes a longer time to upload.

 

e.g. dataset.

Business UnitItem ID
A1
B2
C3
D4
B1
c1
D2
E3

 

Requirement in Slicer :

 

Sum of Item ID 
Item IDAvailable at No. of BUs
13
22
32
41

 

I'll really appreciate the support. *Please be noted that, I need help to work in a DIRECT QUERY mode only. 🙂

 

Regards,

 

Vikrant Singh

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @singhv2,

 

Based on test, it is impossible to create a slicer to filter on summary value 'count unit'. (Since you are in direct query mode, calculate table is not available, calculate function also not support in calculated column. In addition, slicer visual not support measure as datasource)

 

For your scenario, I'd like to suggest you create a visual and modify the build to summary mode 'count', then use visual level filter to achieve your requirement.

6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

 

Thank You.

 

I could've tried this method, however, due to the data size, it's taking a lot of time to get published. 

 

Let me know, if we have some other possibility.

 

Regards,

 

Vikrant

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.

Top Solution Authors
Top Kudoed Authors