Group By measure

[gkakun]

Hi all,

I have a problem with group by measure. 

I have a table called FOI- with Transaction ID (Unique), part number, Supplier Name, location of the part, failure reason, failure timestamp, and more descrptive fields of the part. 

Eventually i need to get a table with supplier name and the number of parts which failed more than twice. 

For example - Supplier XXX-->6 (for this vendor, 6 units failed more than twice) 

Im thinkign to use the group bt with few iterations, but cant find the right way to do that. 

Any idea? 

[iamprajot]

Create a new Table from Modeling,
Table =
SUMMARIZE(FOI,FOI[Supplier_Name],"Supplier Name",IF( CALCULATE(COUNT(FOI[Return]),FILTER(FOI,NOT(ISBLANK(FOI[Return]))))>=2, CALCULATE(COUNT(FOI[Return]),FILTER(FOI,NOT(ISBLANK(FOI[Return])))),BLANK()))

[gkakun]

I have tried this one and got this error (with FOI[IPN] instead if FOI[Return])

Function SUMMARIZE expects a column name as argument number 3.

[iamprajot]

Return is failure reason of your data,
please change the column name according to your data.
also tell me how you identify if a transaction is failed, as basis of that the dax will work.

[gkakun]

Hi, Sure I changed the column names based on the data.

If the part appears in the report its failed, every record in the table is failure record. 

[iamprajot]

in that case above dax will work fine

[gkakun]

Hi, Thanks again. 

Now the formula works but the data is incorrect.

Table = SUMMARIZE(FOI,FOI[IPN],"Supplier Name",IF( CALCULATE(COUNT(FOI[IPN]),FILTER(FOI,NOT(ISBLANK(FOI[IPN]))))>=2, CALCULATE(COUNT(FOI[IPN]),FILTER(FOI,NOT(ISBLANK(FOI[IPN])))),BLANK()))

See for exmpale in the picture, part number 500257585, appears in the new table as 227 times while in the data is appears only 129 times.

[iamprajot]

it isn't clear from the snapshot, what is the issue but looks like MAX and Count cannot result in same value in Totals

[gkakun]

Tge left table counts the number this part failed in the original table and the right table is the new table where you see the number failures of this spesific part is not correct. Its the same result if i dont use the max but dont summarize.

[iamprajot]

then isn't it correct as it counts when there are more than 2 instances.

[gkakun]

Hi, Thanks again for the support. 

i think it may be issue with the formula since i cant see any Supplier in the supplier Name column.

This is the formula

Table = SUMMARIZE(FOI,FOI[IPN],"Supplier Name",IF( CALCULATE(COUNT(FOI[IPN]),FILTER(FOI,NOT(ISBLANK(FOI[IPN]))))>=2, CALCULATE(COUNT(FOI[IPN]),FILTER(FOI,NOT(ISBLANK(FOI[IPN])))),BLANK()))

In the "Supplier Name" column i see only the count of the part

[gkakun]

Thanks for your feedback!

when you say FOI[Return] you mean the unioque field in the table? 

it should count the part number right? 

[gkakun]