cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gkakun Regular Visitor
Regular Visitor

Group By measure

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? 

12 REPLIES 12
gkakun Regular Visitor
Regular Visitor

Re: Group By measure

Capture.PNG

iamprajot Established Member
Established Member

Re: Group By measure

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 Regular Visitor
Regular Visitor

Re: Group By measure

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 Regular Visitor
Regular Visitor

Re: Group By measure

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 Established Member
Established Member

Re: Group By measure

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 Regular Visitor
Regular Visitor

Re: Group By measure

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 Established Member
Established Member

Re: Group By measure

in that case above dax will work fine
gkakun Regular Visitor
Regular Visitor

Re: Group By measure

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 1.PNGas 227 times while in the data is appears only 129 times.

 

 

 

 

iamprajot Established Member
Established Member

Re: Group By measure

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