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

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.