Forum Discussion

Anonymous's avatar
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...
Need Help
  • v-yueyunzh-msft's avatar
    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

v-yueyunzh-msft's avatar
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

Anonymous's avatar
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:

ProductsUser Count
ProductA3
ProductB1
ProductC2
ProductD1
ProductE2
ProductA, ProductB1
ProductA, ProductB, ProductC1
ProductA, ProductE2
ProductB, ProductD0
ProductA, ProductD, ProductE1
… 
.. 
… 
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's avatar
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.