Wrong item in list after one more field added into Matrix

[Anonymous]

Hi there,

In this file, a measure is created to count and identify the Search Terms contains in Domain0 only, but not in Domain1. (Thanks @amitchandak for suggestion)

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

Here is the sample file with Measure created.

The measure works fine in a table contains "Query" only.

But when I add [Page] into the Matrix, the list contains some items which the Query is not only in Domain0.

From above table, you can see "Search Term1" is not ONLY in domain0, but it is listed in below table.

This happens after adding [Page] into Row.

Could you please help to clarify the reason and how to make it works properly?

Thanks in advance.

H

[ERD · Accepted solution]

Hi @Anonymous ,

Here is an option, you need to change the measure:

#Measure =
CALCULATE (
    DISTINCTCOUNT ( Raw[Query] ),
    FILTER (
        SUMMARIZE (
            Raw,
            Raw[Query],
            "d0",
                COALESCE (
                    CALCULATE (
                        DISTINCTCOUNT ( Raw[Domian] ),
                        FILTER ( Raw, Raw[Domian] = "domain0" )
                    ),
                    0
                ),
            "d-total",
                VAR currentQuery = MAX ( Raw[Query] )
                RETURN
                    COALESCE (
                        CALCULATE (
                            DISTINCTCOUNT ( Raw[Domian] ),
                            FILTER ( ALL ( Raw ), Raw[Query] = currentQuery )
                        ),
                        0
                    )
        ),
        [d0] = 1 && [d-total] = 1
    )
)

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

[ERD · Accepted solution]

Hi @Anonymous ,

The measure doesn't affect Impressions, it does what was described in the requirements. If the idea is to filter Impressions, here is the measure (very similar):

#Impressions = 
CALCULATE(
    SUM(Raw[Impressions]),
FILTER (
    SUMMARIZE (
        Raw,
        Raw[Query],
        "d0",
            COALESCE (
                CALCULATE ( DISTINCTCOUNT ( Raw[Domian] ), FILTER ( Raw, Raw[Domian] = "domain0" )),
                0
            ),
        "d-total",
            VAR currentQuery = MAX ( Raw[Query] )
            RETURN
                COALESCE (
                    CALCULATE ( DISTINCTCOUNT ( Raw[Domian] ), FILTER ( ALL ( Raw ), Raw[Query] = currentQuery )),
                    0
                )
    ),
    [d0] = 1 && [d-total] = 1
)
)

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

[ERD · Accepted solution]

Hi @Anonymous ,

Here is an option, you need to change the measure:

#Measure =
CALCULATE (
    DISTINCTCOUNT ( Raw[Query] ),
    FILTER (
        SUMMARIZE (
            Raw,
            Raw[Query],
            "d0",
                COALESCE (
                    CALCULATE (
                        DISTINCTCOUNT ( Raw[Domian] ),
                        FILTER ( Raw, Raw[Domian] = "domain0" )
                    ),
                    0
                ),
            "d-total",
                VAR currentQuery = MAX ( Raw[Query] )
                RETURN
                    COALESCE (
                        CALCULATE (
                            DISTINCTCOUNT ( Raw[Domian] ),
                            FILTER ( ALL ( Raw ), Raw[Query] = currentQuery )
                        ),
                        0
                    )
        ),
        [d0] = 1 && [d-total] = 1
    )
)

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

[Anonymous]

Dear @ERD ，

Sorry to reply you again after weeks.

I just find a situation, in your provided solution, it is comparing cross all "Domains".

For example, there are domain0, domain1, domain2, your solution is filtering out "domain0" ONLY (not in domain1, neither in domain2).

Expect Result:

But is it possible to filter out result compare 2 domains?

For example, filter result in domain0 not in domain1 (compare 2 domains only)

Here is a real example:

Only in domain1 not in domain2 (does not consider domain0).

Here is the sample PBI file.

Thank you again!

H

[ERD]

@Anonymous ,

I'm afraid I can't get what you want to achieve. Is there any way to show the resulting picture on several queries?

Only in domain1 not in domain2 (does not consider domain0).

Does this mean this: 
Seach term N -> domain 0 impressions + domain 1 impressions ?

[Anonymous]

Hi @ERD 

Thanks for your reply, please let me clarify.

From following image you can see, Search Term 10, 11, 20, 3, 4, 6, 7, 8, 9 are in domain0 only.

Search Term 13, 5 are in domain2 only, which your solution perfectly works.

But if

- only check domain1 and domain2 these two domains, we can see Search Term 1 and 12 are having impressions in domain1 only. Seach Term 2 will not be included coz it has impressions in both domain1 and domain2.

- only check domain1 and domain2, Search Term 13, 5 are having impressions in domain2 only

etc..

So that you can see in my OP, in the code, there are 2 places I can set which 2 domains to compare (but this measure does not work well after [Page] is added into visual, as OP said)

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

The excpected result is: When I set to compare domain1 and domain 2, following Seach Term with Page breakdown shows up.

Hopefully I may have your further help.

Thanks and have a great day.

H

[ERD · Accepted solution]

@Anonymous ,

The code that you're showing does the following:
If term contains ONLY domain0, return 1
If term contains domain1 or domain2 or any other domain, return 0.

If that is what you want, and you need to consider Pages, here is the measure:

#Measure_2 =
CALCULATE (
    DISTINCTCOUNT ( Raw[Query] ),
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Raw, Raw[Query], Raw[Page] ),
            "d1",
                COALESCE (
                    CALCULATE (
                        DISTINCTCOUNT ( Raw[Page] ),
                        Raw[Domian] = "domain1",
                        ALL ( Raw[Page] )
                    ),
                    0
                ),
            "d2",
                COALESCE (
                    CALCULATE (
                        DISTINCTCOUNT ( Raw[Page] ),
                        Raw[Domian] = "domain2",
                        ALL ( Raw[Page] )
                    ),
                    0
                )
        ),
        [d1] > 0 && [d2] = 0
    )
)

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

[Anonymous]

@ERD, sorry for replying after 1 week, thank you for the great help! Your solution is perfectly working in sample data.

Although in real data, i meet following error, but I think that's because too many records (20million rows), I will try to find other way.

Have a joyful weekend.

[Anonymous]

Hi @ERD , May I have another question.

After applying this Measure, seems the "Query" field must be in ROW of the table visual, otherwise, will be the wrong number on Impression, is it?

(In fact, the Measure is a workaround to mark & filter the table and to get the Sum of Impressions, and could be breakdown by Date, Page, etc...)

I've applied the "Measure is not Blank" in table visual filter:

Wong result: Query in Column, Day in Row

Wrong Result: Day in Row (no Measure in table visual, but applied Measure is not blank in Filter)

Correct Results:

Query in Row ONLY:

Query & Page in Row:

Query & Date in Row:

Appreciate if you could help to futher clarification.

Thanks.

[ERD · Accepted solution]

Hi @Anonymous ,

The measure doesn't affect Impressions, it does what was described in the requirements. If the idea is to filter Impressions, here is the measure (very similar):

#Impressions = 
CALCULATE(
    SUM(Raw[Impressions]),
FILTER (
    SUMMARIZE (
        Raw,
        Raw[Query],
        "d0",
            COALESCE (
                CALCULATE ( DISTINCTCOUNT ( Raw[Domian] ), FILTER ( Raw, Raw[Domian] = "domain0" )),
                0
            ),
        "d-total",
            VAR currentQuery = MAX ( Raw[Query] )
            RETURN
                COALESCE (
                    CALCULATE ( DISTINCTCOUNT ( Raw[Domian] ), FILTER ( ALL ( Raw ), Raw[Query] = currentQuery )),
                    0
                )
    ),
    [d0] = 1 && [d-total] = 1
)
)

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

[Anonymous]

@ERD Sincerely appreciate your help!

[Anonymous]

Thank you hero! (if you don't mind I call you hero, @ERD )