Forum Discussion

bourne2000's avatar
bourne2000
Helper V
4 years ago

Filter visual by measure not working

Hi   I have a big data which is 3.3 million and coming from AWS Redshift. I connected through direct query   In my table I have a column called Merchant ID. I need to calculate distinct count the...
Need Help
Show and Tell
PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

Try using this measure for the value of the card

 

Distinctcount >1 =
VAR TotMerchants =
    DISTINCTCOUNT ( instore_transactions_la_month[merchant_id] )
RETURN
    COUNTROWS ( FILTER ( instore_transactions_la_month, TotMerchants > 1 ) )

 

  • bourne2000's avatar
    bourne2000
    Helper V
    4 years ago

    PaulDBrown Measure is working. But it gives the entire dataset records which is 3.3 million. It's not filtering.  Thanks for your reply. Kindly advise

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

      Try:

      Merchant Ids > 1 =
SUMX (
    ADDCOLUMNS (
        VALUES ( 'instore_transactions_la_month'[merchant_id] ),
        "Ids",
            IF (
                CALCULATE (
                    COUNT ( 'instore_transactions_la_month'[merchant_id] ),
                    ALLEXCEPT (
                        'instore_transactions_la_month',
                        'instore_transactions_la_month'[merchant_id]
                    )
                ) > 1,
                1
            )
    ),
    [Ids]
)
      • bourne2000's avatar
        bourne2000
        Helper V
        4 years ago

        PaulDBrown Measure is working, however, it shows value 93. Actual value is 269.