Count of rows based on a column's unique id?

[jhinesly]

Hi,

I'm trying to get a volume based on specific columns. I have 3 columns, Date Received, Facility, Status. It looks like this:

Date                                     Facility                                    Status                  Volume (# of Rows where Account # is not a duplicate)

1/1/16                                  Red                                         Complete

1/1/16                                  Red                                         Pending         

1/1/16                                  Blue                                        Pending

1/2/16                                  Red                                         Complete

1/2/16                                  Blue                                        Complete

1/2/16                                  Green                                      Complete

1/2/16                                  Green                                      Pending

[richbenmintz]

I would use the distinctcount function, measure would look like foo=distinctcount('table'[bar])

[jhinesly]

It's close, but it doesn't sort on the current table's rows. It gives the the total for the # of accounts:

Red = 18766

Blue =18766

Green = 18766

I need it to be

Red =8766

Blue = 4000

Green = 6000

 where the total of these would equal that distinctcount of 18766 and also sorted by the dates of the first column

[richbenmintz]

in the distinctcount formula, are you referencing the account column of your data table? are you able to share your pbix file? not sure if you have related tables or really what your data looks like

[jhinesly]

Unfortunately I can't share my data due to the nature of work.

The summary table I've created is 3 columns using the groupby function (Date, Facility, Status) that are from a "Detail Table."

This summary table is missing one column, the volume of accounts sorted by the 3 previously stated columns.

I am using the distinctcount('DetailTable'[Account Number]) which gives the correct total of distinct accounts but does not filter them.

I guess the best way to explain this would be if I used a pivot table of the "Detail Table" in excel and it had rows of:

Date [+]

Facility [+]

Status [+]

and the column would be the count of account numbers

[v-yuezhe-msft]

Hi @jhinesly,

Based on your description, it is difficult for us to provide detailed method. Would you please share dummy data of your table and post expected result here?

Thanks,
Lydia Zhang

[jhinesly]

That is test data that I would like to summarize into knowing on "x" date - Facility A, B, etc.., had total number of volumes for Completed, Completed from Pending log, Pending, Backlog, etc..

Kind of like a pivot table would show:

[v-yuezhe-msft]

Hi @jhinesly,

Based on your pivot table, it seems that you filter the output. What fields do you use to filter the entire output?

Thanks,
Lydia Zhang