How to create a filtered calculated table with more than one column.

[danextian]

Hello,

I'd like to create a calculated table taken from the data below.

What I want  is to get the Case and Count column filtered based on the max value by case in count column.

I do know how to create a calculated table with filtered rows but not if it has more than one column. 

Case	count	Other column	Other column2
Case1	1	value	value
Case2	2	value	value
Case3	3	value	value
Case4	4	value	value
Case5	5	value	value
Case6	2	value	value
Case7	3	value	value
Case8	4	value	value
Case9	1	value	value
Case1	2	value	value
Case2	3	value	value
Case3	1	value	value
Case4	1	value	value
Case5	2	value	value
Case6	4	value	value
Case7	5	value	value
Case8	6	value	value
Case9	7	value	value

[sokg]

Hi @danextian

Try this as a new table

Table = ADDCOLUMNS (
    SUMMARIZE ( Table1, Table1[Case] ),
    "MAXCOUNT", CALCULATE ( MAX ( Table1[count] ) )
)

I think it works.

[Anonymous]

Hi @danextian,

>>I do know how to create a calculated table with filtered rows but not if it has more than one column. 

You can try to use 'AND' or 'OR' function to add these conditions in one filter.

Sample:

//filter table when Column 1 = "111" and Column 2 =2

Result=
CALCULATETABLE('Table',FILTER(ALL('Table'),AND([Column1]="111",[Column2]=2)))

//filter table when Column 1 = "111" or Column 2 =2
Result=
CALCULATETABLE('Table',FILTER(ALL('Table'),OR([Column1]="111",[Column2]=2)))


Another write method :

Result=
CALCULATETABLE('Table',FILTER(ALL('Table'),[Column1]="111"&&[Column2]=2))

Result=
CALCULATETABLE('Table',FILTER(ALL('Table'),[Column1]="111"||[Column2]=2))

Regards,

Xiaoxin Sheng

[danextian]

Hi @Anonymous

Thanks  for the reply.

What I was trying to say was I would like to return just  two columns from the original table to a calculated one - Case and Count columns.  The  criteria is max value in Count  column  based on distinct values in Case column.

[TomMartens]

Hey,

Assuming you want to solve this with DAX:

If you are on Power BI or SSAS 2016 you can go with GROUPBY otherwise you can try SUMMARIZE (be aware that SUMMARIZE has its issues)

The GROUPBY can look like this

GROUPBY('BaseTable',BaseTable[Case],"Count", MAXX(CURRENTGROUP(),'BaseTable'[Count])) 

Hope this helps