Count distinct values with condition in a table variable

[Anonymous]

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, use selectcolumns to get the measures I need. But I'm facing a problem that I couldn't fix until now, I need to sum the distinct values of a specific column, but only if the values in other column match a condition. I should be able to do it with the DISTINCTCOUNT() function, however, since I'm using table variables, I can't refer to a specific column and I can't find other way to do it. Here is a sample data of what I intend to do:

I have mutiple products ID that belong to the same product type and the column "Sales" tell me the quantity of product sales for each product type. If the quantity of sales is greater than 20, the cell in the column "Sum >20" receives 1 and if not, 0. Finally, the last column is the one that I don't know how to create, the idea is to give us the number of products inside the product type that sold more than 20 unities. For example, if we filter the product type food we are going to see "Banana, Pizza and Tomato", the sum will be more than 20 for "Banana" and "Tomato", so the final column should show us the value "2" for that product type, that represents the count of different values shown on product type column. 

Does anyone know how can I achieve that, considering my data is in a table variable?

Thank you!

[AlexisOlson · Accepted solution]

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]

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?