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 , 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's avatar
Anonymous
Not applicable
5 years ago

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

      amitchandakThanks for the quick reply.

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

       

    • 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