Forum Discussion

Postigo's avatar
Postigo
Frequent Visitor
4 years ago
Solved

Using distinctcount as a filter

Hello everyone,

here is a issue i'm facing:

i have two tables (Products and Sales) and i need to have a measure to show me how many products i have that more than 3 different Clients bought(in this case only the top 4 lines will be true)

i already tried something like this


Measure =
var Table1 =
CALCULATETABLE(
SUMMARIZE(
'Product',
'Product'[SK_PRODUCT],
"teste",DISTINCTCOUNT(Sales[SK_CLIENT])
),
DISTINCTCOUNT(Sales[SK_CLIENT])>3
 
)
return

COUNTROWS(Table1)



But it doesn't work, it's possible to solve this with measures? My company data is build in Tabular model and power bi not allows to have two different data sources in this case

File Example
 
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago
    var Table1 =
    Filter(
    SUMMARIZE(
    'Product',
    'Product'[SK_PRODUCT],
    "teste",DISTINCTCOUNT(Sales[SK_CLIENT])
    ),
    [teste]>3)
     
    )
    return

     

    COUNTROWS(Table1)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    var Table1 =
    Filter(
    SUMMARIZE(
    'Product',
    'Product'[SK_PRODUCT],
    "teste",DISTINCTCOUNT(Sales[SK_CLIENT])
    ),
    [teste]>3)
     
    )
    return

     

    COUNTROWS(Table1)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    llo

     

    Why dont you use the filter ?

     

  • Postigo's avatar
    Postigo
    Frequent Visitor
    4 years ago

    Hello JamesFr06, thanks for your reply.

    I can't do that way, what i need is the number of rows of this solution you shared above, in the DAX example i used i tried to create this example as a variable and use the COUNTROWS() on it, but it doesn't work