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: