Create a calculated formula based on distinct values

We are trying to create a logic where we want the distinct count of a dimension when a measure equals a certain value

*when a distinct function is enclosed into a sum function it works as normal count 
*when creating a formula column with if statement, it must be enclosed into a sum function to work, and we face the problem that the distinct works as normal count

below is a sample table to demonstrate: 
create table Test_Table (static nvarchar(10),t nvarchar(10),n int)

insert into Test_Table values('Static','a',1) 
insert into Test_Table values('Static','a',1) 
insert into Test_Table values('Static','a',0) 
insert into Test_Table values('Static','b',1) 
insert into Test_Table values('Static','b',0)


schema.Test_Table.n = 1, 


1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • The Answer is:

    1- Based on the provided sample data create an aggregated table

    2-In the measure column set the Aggregation function to Distinct

    3-In the measure column  in the filter area set the Val =1 



    The output:


    Attached the used data sample in an Excel file.

Like Follow
  • 5 mths agoLast active
  • 1Replies
  • 20Views
  • 1 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!