Calculate distinct items by classification

[prpaz]

Hello friends, I have the following question about creating a measure.

I have two related tables, items and classification. I need a measure that calculates the number of distinct items that have only one classification, example of a table.

table item

cod item |  item 

a1              lop1

a2              lop33

a3              lop77
a4              prp20
a5              kge25

table Classification
cod item |  classification  | item 
a1                orange           prp20

a2                red                 prp20

a3                orange           kge25

a4                blue               lop1
a5                blue               lop33

What measure to use to know the distinct count of only the orange items? just the blue items? just the red items?

[v-tangjie-msft]

Hi @prpaz ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Measure = CALCULATE(DISTINCTCOUNT('table Classification'[item]),FILTER(ALLSELECTED('table Classification'),'table Classification'[classification]=MAX('table Classification'[classification])))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[prpaz]

Hello friends, I still haven't found the solution I need.

Below is an image for better understanding.

I need to know the number of items that have only one type of classification, see that I have 9 different items, but which ones have only one type of classification (graph 3)?

link to power bi.
distinct test.pbix 

[v-tangjie-msft · Accepted solution]

Hi @prpaz ,

We can create measures. 

Flag = 
var _table=SUMMARIZE('classification','classification'[item],'classification'[classification])
var _count=COUNTROWS(FILTER(_table,[item] in VALUES('classification'[item])))
RETURN IF(_count=1,1,0)

count = SUMX(VALUES('classification'[item]),[Flag])

Then the result is as follows, the number of items that have only one type of classification is 7.

Best Regards,

Neeko Tang

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

[prpaz]

Explaining in an easier way, I have several items that have several classifications. But I need the number of items that only have one distinct classification. Example: The number of distinct items that are allocated to the orange classification.

[Dangar332]

Hi, @prpaz in this case what is count for orange?

For red?

For Blue? From data you provide 

[Dangar332]

hi, @prpaz 

try below code 

Measure 3 = 
CALCULATE(DISTINCTCOUNT('item'[item]),TREATAS(VALUES(clas[code item]),'item'[code item]))+DISTINCTCOUNT(clas[code item])

[PijushRoy]

HI @prpaz 

Can you please try the below code in Measure

Measure = 
CALCULATE(DISTINCTCOUNT('table Classification'[classification]),'table Classification'[classification]="orange")

 
Please change the DISTINCTCOUNT column name which you want to count

If solve your requirement, please mark as SOLUTION

Thanks
Pijush

[prpaz]

Hello, an item can have several classifications, but I only need the distinct count of the items in each classification for comparison in the graph, see example:

Orange.... 4

Red................5
Blue.....1