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

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , here measure I used in the formula is the one you are already using in your matrix That can be sum(Raw[Click]) , or something else which you have used

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi amitchandak , I tried to fix the errors, the fixed formula from my hand is:

Measure = CountX(filter(Summarize(Raw, Raw[Query], "_1", calculate(SUM(Raw[Impressions]),filter(Raw,Raw[Domian] ="domain0")), "_2", calculate(SUM(Raw[Impressions]), filter(Raw,Raw[Domian] ="domain1"))), not(isblank([_1])) && isblank([_2])), [Query])

There are 2 situations:

1. There are 3 items in the final list which seems shall not be contained. Is there a way to exclude them (I can see they are not in "Total", but is it possible to just remove them from the list?)

 

 

2. The Measure is to get the count. Is there a way to get the Sum of Impressions? I tried to replace the "Countx" by using "Sumx", but get error in visual:

 

Thanks again for your always generous help.

H