Reply
Frequent Visitor
Posts: 14
Registered: ‎03-15-2017

Creating a calculated column/table with filters

So I have a data source that looks similar to this

 

Customer IDLocationType
100AZ
101AZ
102AX
103BX
104BX
105BZ

 

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

 

LocationDistinct Customers Type ZDistinct Customers Type X
A21
B12

 

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?

Highlighted
Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: Creating a calculated column/table with filters

[ Edited ]

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! Smiley Happy

Super Contributor
Posts: 2,678
Registered: ‎07-17-2016

Re: Creating a calculated column/table with filters

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.Smiley Happy

 

Regards