Unable to use group by for multiple columns

[PooBIDeveloper]

Hi All,

I have a table with my comments  and months columns.

i want each month column should be grouped based on there comments.

i have used groupby but its applied for one column only ie aug,when i am using the same groupby dax for the other column its showing error table of multiple values.

can any one please help .

[Anonymous · Accepted solution]

Hi  @PooBIDeveloper ,

Here are the steps you can follow：

1. Enter the power query, select the column with month identification, such as [17Aug21], [5Jun21], [6Sep21], click Transform – Unpivot Columns.

Result:

2. Create measure.

Count_Measure =
CALCULATE(COUNT('SampleTable'[Value]),FILTER(ALL('SampleTable'),'SampleTable'[Value]=MAX('SampleTable'[Value])&&'SampleTable'[Attribute]=MAX('SampleTable'[Attribute])))

3. Result:

Best Regards,

Liu Yang

[PooBIDeveloper]

Hi Liu Yang it works..thanks a lot...

but  that horizontally total is not showing correctly...

[PooBIDeveloper]

Hi v-yangliu-msft ,

Thanks for your revert

i dont have any date column ,or table below is the sample of the excel from that i have to group the commnets which are in aug,mar and june.

from above excel i have to make belor report

i have achived this but for only column ie aug , but unable to do the same for the other columns

Group_Comments_aug =

GROUPBY(Query1,Query1[17Aug21],"#_AUg",COUNTX(CURRENTGROUP(),1))

i got the below output for one column ie AUg only.

[Anonymous]

Hi  @PooBIDeveloper ,

I created some data:

Here are the steps you can follow：

1. Create measure.

count_group =
COUNTX(FILTER(ALL('Table'),'Table'[comments]=MAX('Table'[comments])&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[comments])

hasonevalue =
var _table=SUMMARIZE('Table', 'Table'[comments] ,"__value", [count_group])
RETURN
IF(HASONEVALUE('Table'[comments]),[count_group],SUMX(_table,[__value]))

2. Put it into the matrix

3. Result:

Best Regards,

Liu Yang

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

[PooBIDeveloper]

Hii..

any suggestions??

[PooBIDeveloper]

if i am using same dax with other column nmae it shows error table of multiple value

[amitchandak]

@PooBIDeveloper , Try to group comments like this

concatenatex(Table, Table[Comments], ",")

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[PooBIDeveloper]

thanks for ur repy.

below is the sample for the same

[amitchandak]

@PooBIDeveloper , the sample is not making the issue clear. Can you explain

[PooBIDeveloper]

ok

I have an excel sheet which have customer names,sales,state,region etc.

in that sheet i have every column name such as 17Aug21,13 jun21,10mar21 and this month column name will be added every month.

in month column name(17Aug21,13june21) i have data such as CANNOT BE APPLIED,CONFIRMATION PENDING:-CUSTOMER AND SP ,CUSTOMER HAS TO ACCEPT THE LINK etc bases on the customer name

so basically i want the count of the comments of every month ie if i select the column 17aug21 in this column if i select value CANNOT BE APPLIED, it will show the values CANNOT BE APPLIED, and i want the count of this value.say its is repeated 10 times for in 17Aug21 .

so             comments                 17aug21  mar21  aug21

              CANNOT BE APPLIED,  10            12          44

i am looking to make the above table.

i have used 

Group_Comments_aug =

GROUPBY(Query1,Query1[17Aug21],"#_AUg",COUNTX(CURRENTGROUP(),1)) which only count the comment of a single column

thanks..

[PooBIDeveloper]

any suggestions....