Forum Discussion
distinct count
Hello ,
I want to count how many parts with supplier more than 2 and supplier equal 1
| Part | Supplier |
| W0030508752R | A |
| W0030508752R | B |
| W0030509095H-V1 | A |
| W0030509095H-V1 | A |
| W0030509095H-V1 | B |
| W41013044 | C |
| W41013621 | C |
| W41017168 | C |
| W41017173 | C |
| W41018408 | C |
| W41018445 | C |
To count how many parts have a supplier more than 2 and a supplier equal to 1, you can use the following DAX formula:
Count = COUNTROWS(FILTER(Table, Table[Supplier] = "1" || CALCULATE(COUNT(Table[Supplier]), ALLEXCEPT(Table, Table[Part])) > 2))Replace "Table" with the name of your table.
This formula uses the FILTER function to create a filter context where the supplier is equal to "1" or where the count of suppliers for the same part is greater than 2. The COUNTROWS function then counts the number of rows that satisfy the filter context.
Note that the ALLEXCEPT function removes all filters from the table except for the ones specified in the ALLEXCEPT arguments. In this case, we are keeping the filters on the "Part" column to ensure that we are counting suppliers for each unique part.
Hi, MSAYED26
You can try the following methods.
Measure:Count Supplier = CALCULATE(DISTINCTCOUNT('Table'[Supplier]),ALLEXCEPT('Table','Table'[Part]))More than 2 = CALCULATE(DISTINCTCOUNT('Table'[Part]),FILTER(ALL('Table'),[Count Supplier]>=2))Equal 1 = CALCULATE(DISTINCTCOUNT('Table'[Part]),FILTER(ALL('Table'),[Count Supplier]=1))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
To count how many parts have a supplier more than 2 and a supplier equal to 1, you can use the following DAX formula:
Count = COUNTROWS(FILTER(Table, Table[Supplier] = "1" || CALCULATE(COUNT(Table[Supplier]), ALLEXCEPT(Table, Table[Part])) > 2))Replace "Table" with the name of your table.
This formula uses the FILTER function to create a filter context where the supplier is equal to "1" or where the count of suppliers for the same part is greater than 2. The COUNTROWS function then counts the number of rows that satisfy the filter context.
Note that the ALLEXCEPT function removes all filters from the table except for the ones specified in the ALLEXCEPT arguments. In this case, we are keeping the filters on the "Part" column to ensure that we are counting suppliers for each unique part.
Hi, MSAYED26
You can try the following methods.
Measure:Count Supplier = CALCULATE(DISTINCTCOUNT('Table'[Supplier]),ALLEXCEPT('Table','Table'[Part]))More than 2 = CALCULATE(DISTINCTCOUNT('Table'[Part]),FILTER(ALL('Table'),[Count Supplier]>=2))Equal 1 = CALCULATE(DISTINCTCOUNT('Table'[Part]),FILTER(ALL('Table'),[Count Supplier]=1))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello V-zhangti,
I working on a similar subject. so I want to ask my question depend this issue:
How can I create a new column for the values "Count Suplier" formula. Other mean, I need this value as a column not in a measure. Because I can't use this measure in axis of a chart 😞
Best Regards
thanks for your efforts
I want to count without repeating the same parts
the example above count of parts more than 1 supplier =2 parts count of parts equal 1 supplier =6 PartsHi,
To your visual, drag Part and this measure. Filter the measure on > 0
More than 2 suppliers = 1*(DISTINCTCOUNT(Data[Supplier])>=2)Hope this helps.
I have done that but the value is fixed with date . How can I make it change from date ?
