Performing count on measure result of virtual table

[DAXimposter]

This is my scenario, I want to first calculate the maxmium occurence of color group by metric, this will be the maximum color. and then get the count of metric group by maximum colour. This has to be done dynamically and results to be changed based on filters. 

Initital Table:

Metric	Region	Color
Metric A	1	G
Metric A	2	G
Metric A	3	R
Metric B	1	R
Metric B	2	G
Metric B	3	R

Given the intial table, when i group by metrics, the color with highest occurence for metric A is G and Metric B is R. This will give return this result:

Metric	Max Color
Metric A	G
Metric B	R

Now, i need to create a matrix table:
using color, it should return
G:1

R:1

And when i filter only on metric A,

it should return

G:1

R:0

This is what i have tried to do:
First create measure to caulcate max_color
max_color = 

CALCULATE(

        MAXX(

            TOPN(

                1,

                SUMMARIZE(

                       Table,

                    Table[METRIC],

                    Table[COLOR],

                    "Color Count", COUNT(Table[COLOR])

                ),

                [Color Count], DESC

            ),

            Table[COLOR]

        ),ALLSELECTED(Table[COLOR]))

Then another measure to do the count:

Color_max_count =

VAR SummarizedTable =

    SUMMARIZE(

        Table,

        Table[METRIC],

        Table[Color],

        "Max_Color", [max_color ]

    )

VAR FilteredTable =

    FILTER(

        SummarizedTable,

        [Max_Color] = Table[Color]

    )

VAR Top1PerMetricTable =

    SUMMARIZE(

        FilteredTable,

        Table[METRIC],

        Table[Color],

        "FirstRow", MIN(Table[Color])

    )

RETURN

COUNTROWS(Top1PerMetricTable)

This is not returning the expected result when i filter on metric A,
it is returning 

G:1

R:1

[v-yangliu-msft]

Thanks for the reply from Jihwan_Kim , please allow me to provide another insight: 
Hi  @DAXimposter ,

Here are the steps you can follow：

1. Create calculated table.

Slicer =
DISTINCT('Data'[Metric])

2. Create measure.

Test_slicer =
var _select=SELECTCOLUMNS('Slicer',"test",'Slicer'[Metric])
return
IF(
    MAX('Data'[Metric]) in _select,1,0)

test_true =
var _table1=
SUMMARIZE(
    ALL('Data'),[Metric],[Color],   "Count",COUNTX(FILTER(ALL('Data'),'Data'[Metric]=EARLIER('Data'[Metric])&&'Data'[Color]=EARLIER('Data'[Color])),[Region]))
var _table2=
FILTER(
    _table1,
    [Count]=MAXX(FILTER(_table1,[Metric]=EARLIER([Metric])),[Count]))
var _table3=
ADDCOLUMNS(
    _table2,"Rank",MAXX(FILTER(ALL('ColorOrder'),'ColorOrder'[Color]=EARLIER([Color])),[ColorOrder]))
var _min=
MINX(FILTER(_table3,[Metric]=MAX('Data'[Metric])),[Rank])
return
MAXX(FILTER(_table3,[Metric]=MAX('Data'[Metric])&&[Rank]=_min),[Color])

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

[DAXimposter]

Thanks but this is not what i need, i need a count of the number of metrics with max color. @Jihwan_Kim solution solves the main problem. 

[DAXimposter]

yes just a simple sum, here's the file: https://drive.google.com/file/d/1XhEDYgRDhanVidViCIrssLOg-tP_6LAx/view?usp=drivesdk
Thanks!

[DAXimposter]

and also to add i have another criteria on the max color, in the case where the count are tied, the selection is based on the order: R,A,G

[DAXimposter]

@Jihwan_Kim solved it! i figure out how to sort by color order. found a workaround for total to just use another visual for it. but it would be good if you have a soultion 🙂 also just for understanding, why "@result" has to be used here?  i thought this is labeling the column name for the expression MAXX(currentgroup(),[@result])

VAR _highestcountcolor =
    GROUPBY ( _t, Metric[Metric], "@result", MAXX ( CURRENTGROUP (), [@result] ) ) // virtually create highest count column

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: =
VAR _currentrowcolorinvisualization =
    MAX ( Color[Color] )
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE ( Data, REMOVEFILTERS ( Color ), REMOVEFILTERS ( Region ) ),
            Metric[Metric],
            Color[Color]
        ),
        "@result", CALCULATE ( COUNTROWS ( Data ) )
    ) // virtually create color count column
VAR _highestcountcolor =
    GROUPBY ( _t, Metric[Metric], "@result", MAXX ( CURRENTGROUP (), [@result] ) ) // virtually create highest count column
VAR _resulttable =
    NATURALINNERJOIN ( _t, _highestcountcolor )
RETURN
    IF (
        HASONEVALUE ( Color[Color] ),
        COUNTROWS (
            FILTER ( _resulttable, Color[Color] = _currentrowcolorinvisualization )
        ) + 0
    )

[DAXimposter]

Thanks! looks like it works! it seems like i have to create a separate table for the filter conditions. 
this will then be used as the refrence to lookup the counts from the data, is that the logic? it wont work if i just use one data table?

and it doesnt haven a total? how do i get it to compute total

[Jihwan_Kim]

Hi,

It will be great if you can share your sample pbix file's link, and then I can learn more about how your semantic model looks like, and I can try to come up with new idea.

And I tried to include HASONEVALUE condition not to show total information, because I do not know how you want to show total. I guess you want to show the simple sum of numbers that are shown above.
Please share your sample pbix file's link, and then I can try to look into it.

Thank you.