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

 

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

6 Replies

  • 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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    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