Forum Discussion

fabiomanniti's avatar
fabiomanniti
Icon for Helper III rankHelper III
4 years ago
Solved

At least one blank

Hello, I have a table with customers and a table with products.

Products and Customers are related as tables.

Now, I can select a group of customers and the number of items within it, is dynamical.

I would like to know if all customer bought at least one product or if there is at least one blank value.

 

I can see

Group 1:

customer_1 | 2

customer_2 | 12

customer_3 | (blank)

 

So I would like a measure like: is group 1 all not blank? True/False

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi fabiomanniti ,

     

    I have built a data sample for test. The two tables are related based on Product ID.

    Firstly, please calculate the distinct count of Product for each Customer. And I used +0 to replace blank with 0:

    Distinct Count = CALCULATE(DISTINCTCOUNT(Customers[Product ID]),FILTER('Customers',[Product ID]<>BLANK()))+0

    So in such case, if the min count <>0 then all are not blank. Please use:

    all not blank? = 
var _t= ADDCOLUMNS('Customers',"Count",[Distinct Count])
var _min=MINX(_t,[Count])
return IF(_min=0,FALSE(),TRUE())

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi fabiomanniti ,

     

    I have built a data sample for test. The two tables are related based on Product ID.

    Firstly, please calculate the distinct count of Product for each Customer. And I used +0 to replace blank with 0:

    Distinct Count = CALCULATE(DISTINCTCOUNT(Customers[Product ID]),FILTER('Customers',[Product ID]<>BLANK()))+0

    So in such case, if the min count <>0 then all are not blank. Please use:

    all not blank? = 
var _t= ADDCOLUMNS('Customers',"Count",[Distinct Count])
var _min=MINX(_t,[Count])
return IF(_min=0,FALSE(),TRUE())

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.