Forum Discussion

bourne2000's avatar
bourne2000
Icon for Helper V rankHelper 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
bourne2000's avatar
bourne2000
Icon for Helper V rankHelper 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
Icon for Community Champion rankCommunity 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
    Icon for Helper V rankHelper V
    4 years ago

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

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Please share some (fake) data to work out the correct measure