How to summarize virtual table table

[stribor45]

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] )
)

[v-nmadadi-msft]

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.

[tamerj1]

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 ) )

[stribor45]

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

[tamerj1 · Accepted solution]

@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 ) )

[gmsamborn]

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?

[stribor45]

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]