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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBOBOP
Frequent Visitor

Searching if one item has more than one label

Hello, 

I have a list of items in our warehouse.

Items can sometimes have more than one row if they have different locations or labels. There are 3 types of labels: R, P, and F

 

Is there any measure that can show:
1) How many items only have the label "F"?
2) How many items that have the label " R" in more than one location?

3) How many items have labels "R" /" P" and "F"?

My sample data:

Sample Data 

1 ACCEPTED SOLUTION

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1717979369646.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Access Denied message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry about that, I have updated access.

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1717979369646.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Thank you for your solution.  It has answered question 2. 


I have made some modifications to the measures you made to solve question 1 and 3.

For question 3, I meant that I would like to count items which have labels of the combination R&F, or P&F. I do not want to count items which only have labels R & P (do not have any labelled F). In the sample data, there are 12 of these items, I have highlighted them in red in the "Question 1 & 3" sheet .
I modified the measure you created "Items with all labels" to 
Items which have 2 labels=

COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Item number]),Data[Item number],"A",[Label count in full data]),[A]=2)).
and it seems to work.
 

For question 1, when I click F on the slicer,

It says that there are 26 items that only have the label F.

However,  there are only 13 items that only have the label F and do not have any other label. I have highlighted them in green on a seperate sheet "Question 1 & 3" on the google sheets link:

PBOBOP_1-1718010124034.png

I think the solution to question 1 would be to take the Item count - the items which have 2 labels.

 

Thank you once again! 

You are welcome.  Try this measure

Items which have 1 label=

COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Item number]),Data[Item number],"A",[Label count in full data]),[A]=1))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Is it possible to drill through to 3 different pages that show a table with all the Item numbers that have been counted with the respective measures?

PBOBOP_1-1718096802595.png

I have added the measure to the drill through field in a different table, but when I drill through, it just shows all the items with the label selected on the slicer in the first page (26 items instead of 13).

PBOBOP_2-1718097075220.png

 

I am unable to upload a PBI file sorry!

Hi @PBOBOP - Yes, it is possible to drill through to multiple pages in Power BI and display a table with all the item numbers that have been counted, along with their respective measures. However, to achieve this correctly, you need to ensure that the filters are being passed accurately to the drill-through pages.

create seperate pages as like below add one table chart for detailed view. 

 

 

rajendraongole1_0-1718114072743.png

 

 

In new page, go to format , page informaiton choose the option drill through and add the required metric or category. back button at top will get appear one you added the drill through metric/category

 

rajendraongole1_1-1718114147122.png

 

go back to your main page and check on the metric, you can see an option drill through to profit page is my example.like that you can implement for remaining pages too.

 

rajendraongole1_2-1718114271726.png

 

I hope it helps, 

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

 

 

I do not have much experience with the drill through feature.  May be someone else will help.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors