Filter table Visual

[Anonymous]

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 in the list, the distinct count shall be 9.

Here's the sample PBI file.

Here's how raw data looks like:

Thank you!

H

[v-deddai1-msft · Accepted solution]

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]

@Anonymous , Try a measure like

countx(filter(Summarize(Table, Table[Query], "_1", calculate([Measure], filter(Table[domain] ="domain0")), "_2", calculate([Measure], filter(Table[domain] ="domain1")))
, not(isblank([_1])) && isblank([_2])), [Query])

[Anonymous]

Hi @amitchandak , glad to see you again and thanks for reply.

But seems there are errors after applying. (I've changed the table name)

Here are the fields in the PBI file if it's not convenience for you to download and open.

Appreciate if you could further help.

Have a good day.

[amitchandak]

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

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

[Anonymous]

@amitchandakThanks for the quick reply.

I tried to replace [Measure], but seems still some errors: