Searching if one item has more than one label

[PBOBOP]

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 

[Ashish_Mathur]

Access Denied message.

[PBOBOP]

Sorry about that, I have updated access.

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[PBOBOP]

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:

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

Thank you once again! 

[Ashish_Mathur]

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))

[PBOBOP]

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?

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).

I am unable to upload a PBI file sorry!

[rajendraongole1]

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. 

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

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.

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!!

[Ashish_Mathur]

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