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 merchant ID and create a another measure by counting the dictinct count greater than 1.  I created two measures

 

Total Merchants = DISTINCTCOUNT(instore_transactions_la_month[merchant_id])

 

Create another Measure called Flag

 

Flag = IF([Total Merchants] > 1, TRUE, FALSE)
 
Now I drag the Total Merchants into the card and filter the visual by dragging "flag" measure into filter on this visual. However, I am not able to explan the flag measure on the filter visual. I need to filter only "True"
 

 

 

Measure is not working on this filter on this visual. Can anyone advise what is the issue? Is there something problem with direct query. My data is quite big. 

Need Help
Show and Tell

8 Replies

  • 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]
)
  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    bourne2000 

    Modify your 2nd measure as follows and assign it equal to 1

    Flag = IF([Total Merchants] > 1, 1 , 0 )

     

     

     

    • bourne2000's avatar
      bourne2000
      Helper V
      4 years ago

      Fowmy Thanks. I modified the measure. However, I am not able to change the filter. It is disabled.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  bourne2000 ,

    I created some data:

    You may build a slicer Table as below and build a measure to achieve your goal : show different result if you select different options.

    1. Use Enter data to create a slice table.

    2. Create measure.

    Total Merchants =
DISTINCTCOUNT('instore_transactions_la_month'[Merchant ID])
    Flag =
IF(
    [Total Merchants]>1,1,0)
    Total =
var _table=SUMMARIZE('instore_transactions_la_month',[Group],"1",[Total Merchants],"2",[Flag])
return
IF(SELECTEDVALUE('Table'[option])=TRUE(),SUMX(FILTER(_table,[2]=1),[Total Merchants]),SUMX(FILTER(_table,[2]=0),[Total Merchants])
)

    3. Result:

    When the slicer is True:

    When the slicer is False:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly