topic Re: Performing count on measure result of virtual table in DAX Commands and Tips

Performing count on measure result of virtual table

DAXimposter — Tue, 17 Sep 2024 03:00:03 GMT

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(

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

Re: Performing count on measure result of virtual table

Jihwan_Kim — Tue, 17 Sep 2024 03:26:17 GMT

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 )

Re: Performing count on measure result of virtual table

DAXimposter — Tue, 17 Sep 2024 05:45:47 GMT

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

Re: Performing count on measure result of virtual table

Jihwan_Kim — Tue, 17 Sep 2024 16:40:08 GMT

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.

Re: Performing count on measure result of virtual table

DAXimposter — Wed, 18 Sep 2024 06:48:35 GMT

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

Re: Performing count on measure result of virtual table

DAXimposter — Wed, 18 Sep 2024 07:14:25 GMT

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

Re: Performing count on measure result of virtual table

DAXimposter — Thu, 19 Sep 2024 07:48:25 GMT

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

Re: Performing count on measure result of virtual table

Anonymous — Mon, 23 Sep 2024 08:36:23 GMT

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

Re: Performing count on measure result of virtual table

DAXimposter — Wed, 25 Sep 2024 03:55:06 GMT

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.