Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count distinct values with condition in a table variable

Hi everyone,    I'm currently trying to create a measure that is a little bit complex and it's data is considerably large. The best way I found to do it was to use table variables and, after that, ...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can use SUMMARIZE to get a table of distinct product names from a table variable and then count the rows of that table.

     

    For example,

    VAR _Tbl = SELECTCOLUMNS ( ... )
VAR _ExpandedTbl =
    ADDCOLUMNS (
        _Tbl,
        "Count Product Name",
            VAR _ID = [Product ID]
            VAR _IDSubtable20 =
                FILTER ( _Tbl, [Product ID] = _ID && [Sum > 20] = 1 )
            RETURN
                COUNTROWS ( SUMMARIZE ( _IDSubtable20, [Product Name] ) )
    )
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You can use SUMMARIZE to get a table of distinct product names from a table variable and then count the rows of that table.

 

For example,

VAR _Tbl = SELECTCOLUMNS ( ... )
VAR _ExpandedTbl =
    ADDCOLUMNS (
        _Tbl,
        "Count Product Name",
            VAR _ID = [Product ID]
            VAR _IDSubtable20 =
                FILTER ( _Tbl, [Product ID] = _ID && [Sum > 20] = 1 )
            RETURN
                COUNTROWS ( SUMMARIZE ( _IDSubtable20, [Product Name] ) )
    )