Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter table Visual

Hello everyone,   Is there a way to filter the table to list: The Query only existing in domain0 but not in domain1? And to get the distinct count? e.g. following highlighted Query are the wanted...
Need Help
Show and Tell
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi  Anonymous ,

     

    Use the following two measures:

    Measure =
COUNTX (
    FILTER (
        SUMMARIZE (
            Raw,
            Raw[Query],
            "sumdomain0",
                IF (
                    CONCATENATEX (
                        CALCULATETABLE (
                            VALUES ( Raw[Domian] ),
                            FILTER ( Raw, Raw[Query] = EARLIER ( Raw[Query] ) )
                        ),
                        [Domian],
                        ","
                    ) = "domain0",
                    SUM ( Raw[Impressions] )
                )
        ),
        NOT ( ISBLANK ( [sumdomain0] ) )
    ),
    Raw[Query]
)


Measure1 =
SUMX (
    FILTER (
        SUMMARIZE (
            Raw,
            Raw[Query],
            "sumdomain0",
                IF (
                    CONCATENATEX (
                        CALCULATETABLE (
                            VALUES ( Raw[Domian] ),
                            FILTER ( Raw, Raw[Query] = EARLIER ( Raw[Query] ) )
                        ),
                        [Domian],
                        ","
                    ) = "domain0",
                    SUM ( Raw[Impressions] )
                )
        ),
        NOT ( ISBLANK ( [sumdomain0] ) )
    ),
    [sumdomain0]
)

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi  Anonymous ,

 

Use the following two measures:

Measure =
COUNTX (
    FILTER (
        SUMMARIZE (
            Raw,
            Raw[Query],
            "sumdomain0",
                IF (
                    CONCATENATEX (
                        CALCULATETABLE (
                            VALUES ( Raw[Domian] ),
                            FILTER ( Raw, Raw[Query] = EARLIER ( Raw[Query] ) )
                        ),
                        [Domian],
                        ","
                    ) = "domain0",
                    SUM ( Raw[Impressions] )
                )
        ),
        NOT ( ISBLANK ( [sumdomain0] ) )
    ),
    Raw[Query]
)


Measure1 =
SUMX (
    FILTER (
        SUMMARIZE (
            Raw,
            Raw[Query],
            "sumdomain0",
                IF (
                    CONCATENATEX (
                        CALCULATETABLE (
                            VALUES ( Raw[Domian] ),
                            FILTER ( Raw, Raw[Query] = EARLIER ( Raw[Query] ) )
                        ),
                        [Domian],
                        ","
                    ) = "domain0",
                    SUM ( Raw[Impressions] )
                )
        ),
        NOT ( ISBLANK ( [sumdomain0] ) )
    ),
    [sumdomain0]
)

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai