Creating a calculated column/table with filters

[thmonte]

So I have a data source that looks similar to this

Customer ID	Location	Type
100	A	Z
101	A	Z
102	A	X
103	B	X
104	B	X
105	B	Z

And I am trying to get a distinct count of values based on the location similar to this.

Location	Distinct Customers Type Z	Distinct Customers Type X
A	2	1
B	1	2

I was able to create a calucated table to give me a row of distinct values in Location so I have the starting column but I am having a hard time filling the rest of the columns with the correct data.  Am I going about this the correct way?  And will this data update constantly as the data source gets updated?

[v-ljerr-msft]

Hi @thmonte,

---

I was able to create a calucated table to give me a row of distinct values in Location so I have the starting column but I am having a hard time filling the rest of the columns with the correct data.  Am I going about this the correct way?  And will this data update constantly as the data source gets updated? 

The data in a calculated column/table will update constantly as the data source gets updated. Because the calculated column/table is computed during the database processing(like a data refresh) and then stored in the model. However, it cannot be affected by user selections on the report.

As suggested by Sean above, using a measure or Matrix visual should work in your scenario.

Regards

[Sean]

This what your Measures should look like

Distinct Customers TypeZ =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer ID] ),
    FILTER ( 'Table', 'Table'[Type] = "Z" )
)

However if you don't want to build individual Measures for each Type

Create a Matrix - place Location in the Rows, Type in the Columns and drag CustomerID to the Values

right click on CustomerID to change the built-in aggregation to Count(Distinct)

You should get the result you posted above!