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
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity 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
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