Microsoft BI Desktop DAX Formula

[amirzoyan]

Hi, 

I am hoping somebody can provide some direction on a problem I am having. 

I am trying to create a report in BI Desktop. I've loaded the data that has multiple columns. 

Specifically - here is an example:

Product Hierarchy 1	Product Hierarchy 2	Value
Product X	Consumables 1	$$$
Product X	Consumables 2	$$$
Product X	Hardware 1	$$$
Product X	Hardware 2	$$$

I am trying to create two new columns for Product X. One column combines hardware 1&2 along with its values, and a second column combining consumables 1&2 from column "Product Hierarchy 2". 
Ultimately, the visual I am trying to achieve in BI should look like this:

Product X	Value
Consumables (1&2)	$$$
Hardware (1&2)	$$$

Any suggestions on the DAX formula I should be using to achieve this?

[v-deddai1-msft · Accepted solution]

Hi @amirzoyan , 

Would you please follow the steps in the link :https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESGiM9r4KfxLuiK-6U...

Best Regards,

Dedmon Dai

[amitchandak]

@amirzoyan 

You can create a new column

switch(true(),
search("Consumables",[Product Hierarchy 2],,0)>0 ,"Consumables (1&2)",
search("Hardware",[Product Hierarchy 2],,0)>0 ,"Hardware (1&2)",
"Others"
)

Or refer :https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[VijayP]

you can create Groups