Forum Discussion
Checking multiple columns and visualizing aggregated value when condition met in multiple columns
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
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.
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
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.