Hi there, At first I'm sorry for vague subject; couldn't come up with a proper subject 😞 My data has few dimensions containing "Y" or "N" values. I would like to count rows where one or more of the dimensions have "Y" values.For instance, the data looks like NameProductAProductBProductCProductDProductESamYNNNYAlexYYYNNSmithNNNNNJohnYNYYYI already have measures that count Y for individual products (A, B, C, D, E) and also mapped those measures to a Product table, which looks like,Product_IDProduct_Name1ProductA2ProductB3ProductC4ProductD5ProductEThis setup is working when I want to answer questions about specific product. But, I'm trying to answer questions like, how many users have used ProductA & ProductB (and any number of combinations possible)? I can manually create a table for all possible combinations and create similar measures for all combinations, but I don't think that's anywhere near an optimal solution. Thanks in advance for any ideas or hints!

Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Checking multiple columns and visualizing aggregated value when condition met in multiple columns

Hi there,

At first I'm sorry for vague subject; couldn't come up with a proper subject 😞

My data has few dimensions containing "Y" or "N" values. I would like to count rows where one or more of the dimensions have "Y" values.

For instance, the data looks like

Name	ProductA	ProductB	ProductC	ProductD	ProductE
Sam	Y	N	N	N	Y
Alex	Y	Y	Y	N	N
Smith	N	N	N	N	N
John	Y	N	Y	Y	Y

I already have measures that count Y for individual products (A, B, C, D, E) and also mapped those measures to a Product table, which looks like,

Product_ID	Product_Name
1	ProductA
2	ProductB
3	ProductC
4	ProductD
5	ProductE

This setup is working when I want to answer questions about specific product.

But, I'm trying to answer questions like, how many users have used ProductA & ProductB (and any number of combinations possible)?

I can manually create a table for all possible combinations and create similar measures for all combinations, but I don't think that's anywhere near an optimal solution.

Thanks in advance for any ideas or hints!

Need Help

v-yueyunzh-msft

3 years ago

Hi , Anonymous

For you to present the combined data in a table, this is not very easy to implement, because we are placing fields, which are not automatically generated in Power BI.

For your requirements, I have modified my measures and implemented your requirements, you can refer to ,Here are the steps you can refer to :

(1)We need to update the two measures:

How many people = var _t =FILTER( 'Table' , 'Table'[Value]="Y")
var _slicer_product =COUNTROWS( VALUES('Table'[Product]))
var _total_t  = SUMMARIZE( 'Table' , 'Table'[Name], "count" , CALCULATE( COUNT('Table'[Product]) , 'Table'[Value] = "Y"),"slice" , _slicer_product)
var _t2 =COUNTROWS( FILTER( _total_t , [count]= [slice]))
return
IF( HASONEVALUE('Table'[Product]) , COUNTROWS(_t),_t2 )

who people = 
var _slicer_product =COUNTROWS( VALUES('Table'[Product]))
var _total_t  = SUMMARIZE( 'Table' , 'Table'[Name], "count" , CALCULATE( COUNT('Table'[Product]) , 'Table'[Value] = "Y"),"slice" , _slicer_product)
var _t2 = FILTER( _total_t , [count]= [slice])
return
CONCATENATEX(_t2 , [Name],",")

(2)For your need , the visual in card , you can use this dax :

Accounts = var _slicer_product =COUNTROWS( VALUES('Table'[Product]))
var _total_t  = SUMMARIZE( 'Table' , 'Table'[Name], "count" , CALCULATE( COUNT('Table'[Product]) , 'Table'[Value] = "Y"),"slice" , _slicer_product)
var _t2 =COUNTROWS( FILTER( _total_t , [count]= [slice]))
return
_t2

(3)The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

1027accounts.pbix28 KB

5 Replies

v-yueyunzh-msft
Community Support
3 years ago
Hi , Anonymous

According to your description, you want to realize " how many users have used ProductA & ProductB". Right?

Here are the steps you can refer to :

(1)My test data is the sam as yours, We can unpivot the table in Power Query Editor:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MVdJRigRiPyQcqRSrE63kmJNaAZWMRFEEkgzOzSzJQNOGkPXKz8hD0oIwIjYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ProductA = _t, ProductB = _t, ProductC = _t, ProductD = _t, ProductE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ProductA", type text}, {"ProductB", type text}, {"ProductC", type text}, {"ProductD", type text}, {"ProductE", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Product", "Value") in #"Unpivoted Columns"

Then we can get the table like this:

(2)We can click two measure in Power BI Desktop:

How many people = var _t = FILTER( 'Table' , 'Table'[Value] = "Y" ) return COUNTROWS(_t)
who people = var _t = FILTER( 'Table' , 'Table'[Value] = "Y" ) return CONCATENATEX(_t , [Name],",")

(3)Then we can put the filed on the visual and we can put the [product] in the slicer, we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1026_product.pbix26 KB
- Anonymous
  Not applicable
  3 years ago
  HI v-yueyunzh-msft ,
  
  Thanks for your quick answer. In your output, the total shows 6; however, the answer I am looking for will be 1 since Alex is the only who uses all three products.
  
  The output we are trying to find will look like this if we put in a tabular format:
  Products User Count
  ProductA 3
  ProductB 1
  ProductC 2
  ProductD 1
  ProductE 2
  ProductA, ProductB 1
  ProductA, ProductB, ProductC 1
  ProductA, ProductE 2
  ProductB, ProductD 0
  ProductA, ProductD, ProductE 1
  …
  ..
  …
  etc
  
  We intend to have a slicer and Card visual. Depending on the slicer selection, the card visual should show the number of accounts who have "Y" for the selected products. Now selecting multiple simply add the measures for individual products, which produces
  When two products are selected total is showing 196k. We don't want to see this number, rather we want to see the number of accounts who are active in both products; it should be somewhere between 80-90k.
  - v-yueyunzh-msft
    Community Support
    3 years ago
    Hi , Anonymous
    
    For you to present the combined data in a table, this is not very easy to implement, because we are placing fields, which are not automatically generated in Power BI.
    
    For your requirements, I have modified my measures and implemented your requirements, you can refer to ,Here are the steps you can refer to :
    
    (1)We need to update the two measures:
    
    How many people = var _t =FILTER( 'Table' , 'Table'[Value]="Y") var _slicer_product =COUNTROWS( VALUES('Table'[Product])) var _total_t = SUMMARIZE( 'Table' , 'Table'[Name], "count" , CALCULATE( COUNT('Table'[Product]) , 'Table'[Value] = "Y"),"slice" , _slicer_product) var _t2 =COUNTROWS( FILTER( _total_t , [count]= [slice])) return IF( HASONEVALUE('Table'[Product]) , COUNTROWS(_t),_t2 )
    who people = var _slicer_product =COUNTROWS( VALUES('Table'[Product])) var _total_t = SUMMARIZE( 'Table' , 'Table'[Name], "count" , CALCULATE( COUNT('Table'[Product]) , 'Table'[Value] = "Y"),"slice" , _slicer_product) var _t2 = FILTER( _total_t , [count]= [slice]) return CONCATENATEX(_t2 , [Name],",")
    
    (2)For your need , the visual in card , you can use this dax :
    
    Accounts = var _slicer_product =COUNTROWS( VALUES('Table'[Product])) var _total_t = SUMMARIZE( 'Table' , 'Table'[Name], "count" , CALCULATE( COUNT('Table'[Product]) , 'Table'[Value] = "Y"),"slice" , _slicer_product) var _t2 =COUNTROWS( FILTER( _total_t , [count]= [slice])) return _t2
    
    (3)The result is as follows:
    
    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
    
    Best Regards,
    
    Aniya Zhang
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
    1027accounts.pbix28 KB
Shaurya
Memorable Member
3 years ago
Hi Anonymous,

I am not going to assume the different combinations that you may or may not have to use but if it's an idea that you want, I suggest that you write conditions for count of different product combinations like number of rows with atleast 3 products or atmost 4 products. You can create a slicer for a passsing the number like 3 or 4 in this case and use that in the conditions. That way, it wouldn't be hardcoded and it will siginificantly reduce the number of conditions.

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison

Forum Discussion

Checking multiple columns and visualizing aggregated value when condition met in multiple columns

5 Replies

Related content

Data Aggregation issue

Changing aggregation condition based on condition

Aggregate

Conditional aggregation / filter

Aggregating conditional distinct counts

Products	User Count
ProductA	3
ProductB	1
ProductC	2
ProductD	1
ProductE	2
ProductA, ProductB	1
ProductA, ProductB, ProductC	1
ProductA, ProductE	2
ProductB, ProductD	0
ProductA, ProductD, ProductE	1
…
..
…
etc