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

[mohsenalam]

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!

[v-yueyunzh-msft]

Hi , @mohsenalam 

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

[mohsenalam]

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 · Accepted solution]

Hi , @mohsenalam 

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

[mohsenalam]

Thanks a lot @v-yueyunzh-msft !

I can see that this will work. If it's not too much of asking, can you please explain how it is working? I see that I have to work a lot in my DAX skills!

Btw, I have kinda accomplished this in another way, not sophisiticated like you of course. I grouped "Y" values from each of the product columns to match the product name. Then, I made a slicer panel with slicer for each of the new group, but kept only the "Y" value in the slicer. After putting them in a horizontal layout, it almost looks like one slicer and showing the intended result. 

I will try out your solution as well.
Thanks again.

[Shaurya]

Hi @mohsenalam,

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