Filter visual by measure not working

[bourne2000]

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. 

[Anonymous]

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

[Fowmy]

@bourne2000 

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

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

[bourne2000]

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

[PaulDBrown]

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]

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

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]

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

[PaulDBrown]

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