Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Count with Groupby

Hi,

I have the below table containing Work order number, with some status field. What I am trying to do is to create a summary table of Status with count of work orders.

The measure that I have created is:

 

Output = COUNTX('WO History Dimensions', 'WO History Dimensions'[WO History Status])

 

This is quite close to what I am looking for but what I want is to create count with some sort of group by on Work Order Number.

E.g. The status "70-PARTS ISSUED" is having output = 3 but it should be 2 as it should consider WO number 43-701442 only once.

Can someone please help me with my dax formula for Output measure?

 

Please also note that "WO Number" field is coming from WO Dimension and "WO History Status" field is coming from WO History Dimension and there is a 1-* relation between them.

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    If a straight DISTINCTCOUNT on the WO Number column won't work, you can use this expression to make the filter "flow uphill".

    NewMeasure = CALCULATE(DISTINCTCOUNT(WO Dimension[WO Number]), 'WO History Status')

     

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Pat, I tried this but getting this error. Sorry I am newbie in DAX 😞

       

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,I think you can put a slicer in the report, drag into 'wo number' field, then the data in the table will automatically be counted according to the number you have selected.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.