Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thmonte
Helper IV
Helper IV

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?

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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

Sean
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.