Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
1 year ago
Solved

How to summarize virtual table table

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] )
)
  • tamerj1's avatar
    tamerj1
    1 year ago

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

6 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    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's avatar
      stribor45
      Post Prodigy
      1 year ago

      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]

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    1 year ago

    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's avatar
      stribor45
      Post Prodigy
      1 year ago

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

      • tamerj1's avatar
        tamerj1
        Community Champion
        1 year ago

        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 ) )
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    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.