Forum Discussion

prpaz's avatar
prpaz
Regular Visitor
2 years ago
Solved

Calculate distinct items by classification

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?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

8 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    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's avatar
      prpaz
      Regular Visitor
      2 years ago

      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

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, prpaz 

    try below code 

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

     

     

  • prpaz's avatar
    prpaz
    Regular Visitor
    2 years ago

    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's avatar
      Dangar332
      Resident Rockstar
      2 years ago

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

      For red?

      For Blue? From data you provide 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      prpaz
      Regular Visitor
      2 years ago

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.