topic Re: How to summarize virtual table table in DAX Commands and Tips

How to summarize virtual table table

stribor45 — Sat, 26 Apr 2025 01:59:41 GMT

I am trying to summarize this virtual table (non base table) and of course I am getting error that the base table is expected. Can someone assist please.

DEFINE VAR C = ADDCOLUMNS ( DISTINCT ( UNION ( DISTINCT ( 'A'[ID] ), DISTINCT ( 'B'[ID] ) ) ), "@COUNTRY", MINX ( FILTER ( ADDRESS, ADRESS[USERID] = FORMAT ( [ID], "0" ) ), RELATED ( 'Flags'[Country] ) ) ) EVALUATE SUMMARIZE ( C, C[Country], "Count", COUNT ( C[ID] ) )

Re: How to summarize virtual table table

gmsamborn — Sat, 26 Apr 2025 03:33:54 GMT

Hi stribor45

Without sample data, this is just a guess:

EVALUATE SUMMARIZE( ADDCOLUMNS ( DISTINCT ( UNION ( DISTINCT ( 'A'[ID] ), DISTINCT ( 'B'[ID] ) ) ), "@COUNTRY", MINX ( FILTER ( ADDRESS, ADRESS[USERID] = FORMAT ( [ID], "0" ) ), RELATED ( 'Flags'[Country] ) ) ), [Country], "Count", COUNT ( [ID] ) )

If that doesn't help, could please supply sample data to work with?

Re: How to summarize virtual table table

stribor45 — Sat, 26 Apr 2025 10:05:13 GMT

I know without data is hard. I tried that before I posted as I was pretty sure it will work but kept complaining that it cant find [ID]

Re: How to summarize virtual table table

tamerj1 — Sat, 26 Apr 2025 16:20:51 GMT

Hi stribor45

please try

DEFINE VAR C = ADDCOLUMNS ( DISTINCT ( UNION ( DISTINCT ( 'A'[ID] ), DISTINCT ( 'B'[ID] ) ) ), "@COUNTRY", MINX ( FILTER ( ADDRESS, ADRESS[USERID] = FORMAT ( [ID], "0" ) ), RELATED ( 'Flags'[Country] ) ) ) EVALUATE GROUPBY ( C, [Country], "Count", SUMX ( CURRENTGROUP (), 1 ) )

Re: How to summarize virtual table table

stribor45 — Sun, 27 Apr 2025 00:10:33 GMT

I tried it and error received was COUNTRY specified in the GROUPBY function was not found in the table

Re: How to summarize virtual table table

tamerj1 — Sun, 27 Apr 2025 18:40:50 GMT

stribor45

Missed the @ sign 😅

DEFINE VAR C = ADDCOLUMNS ( DISTINCT ( UNION ( DISTINCT ( 'A'[ID] ), DISTINCT ( 'B'[ID] ) ) ), "@COUNTRY", MINX ( FILTER ( ADDRESS, ADRESS[USERID] = FORMAT ( [ID], "0" ) ), RELATED ( 'Flags'[Country] ) ) ) EVALUATE GROUPBY ( C, [@Country], "Count", SUMX ( CURRENTGROUP (), 1 ) )

Re: How to summarize virtual table table

Anonymous — Tue, 06 May 2025 02:25:52 GMT

Hi stribor45 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.