Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have the following simple table where I should count how many Store IDs have more than 1 client (the result should be 2 because S3 and S6 are the stores with more than 1 customer).
Store ID | count(distinct[Client]) |
S1 | 1 |
S2 | 1 |
S3 | 2 |
S4 | 1 |
S5 | 1 |
S6 | 4 |
It seems really simple, but my problem is that the Measure is obtained through set analysis, and I can't create any variable in my data.
Is there any way to make this calculation?
I'm trying something like the following but it's not working:
count({<count(distinct [Client])={">1"}>} [Store ID])
Thanks in advance!
The set analysis, expression you have provided seems to be incorrect. You can't use count function within the set analysis. Maybe you can try this
count({< [Product/Material id] = {"=count(distinct [Client])>1"} >} [Product/Material id])
thanks for the hint.
Unfortunately the formula is returning 0
Hi @rm1 , I can see that the problem statement was changed when I answered your question. Since you have used a dimension [Product/Material id] in your set expression so I answered it accordingly.
if its only related to Store ID then you can try this
{< Client={">1"} >} [Store ID]
{< [Store ID] = {"=Count(DISTINCT Client) > 1"} >} [Store ID]
@rm1 try below
count({<[Store ID]={"=count(distinct [Client])>1"}>} [Store ID])
or
sum(aggr(if(count(distinct [Client])>1,count(distinct [Client]),0),[Store ID]))