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] ) )
    )
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 

what do you mean by table variables? Do you mean a summary table visual? Which fields are columns and which fields are calculated columns and which fields are measures?