Forum Discussion

lafakios's avatar
lafakios
Helper I
3 years ago
Solved

Create calculated table with filtered distinct rows

Hi,

 

I want to create a calculated table based on a dimension table.

The dimension table has several rows and some of the rows share the same value in the first column.  

 

In the calculated table, I want to only have the distinct values of the first column of the dimension table.

The command that I am now using looks like the below and works fine.

 

GENERATE(DISTINCT('DimensionTable'[FirstColumn]),SUMMARIZE('DimensionTable', "Count",  CALCULATE(COUNT('DimensionTable'[SecondColumnn]))))

 

The dimension table has a column called IsActive=True/False, that I want to use for filtering out all inactive rows in the dimension table. In other words, I want the calculated table with distinct values to not include the values that are set as inactive in the dimension table.

 

How should I use the Generate command for achieving this type of filtering?

 

Thanks in advance,

Akis

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi lafakios 

    It doesn't look like a use case of GENERATE. Simply use

    NewTable =
    SUMMARIZE (
    FILTER ( 'DimensionTable', 'DimensionTable'[IsActive] = "True" ),
    'DimensionTable'[FirstColumn],
    "Count", COUNT ( 'DimensionTable'[SecondColumnn] )
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi lafakios 

    It doesn't look like a use case of GENERATE. Simply use

    NewTable =
    SUMMARIZE (
    FILTER ( 'DimensionTable', 'DimensionTable'[IsActive] = "True" ),
    'DimensionTable'[FirstColumn],
    "Count", COUNT ( 'DimensionTable'[SecondColumnn] )
    )