Filter card by a measure

[mmp]

Hello,

I have a disconnected table that gives the active/inactive rows. From that i created two measures in order to filter correctly:

Statut Win Back 1 = IF([Sales Last Period WB1 60] >0 && [Sales Last Period WB1 30]<=0,"Actif", "Inactif")

Filtre actif win back 1 = 
var actif = FIRSTNONBLANK(BISTATUTFILTRE[Statut],BISTATUTFILTRE[Statut]="Actif")
var inactif = LASTNONBLANK(BISTATUTFILTRE[Statut],BISTATUTFILTRE[Statut]="Inactif")
var currentState = [Statut Win Back 1]

RETURN if(currentState=actif || currentState=inactif,1,0)

I'm applying the Filtre actif win back 1=1 in a table at the visual filter level for one table and it works great. However, I'm facing some issues with card visual, i'm not able to do that and the result is wrong.

The following measure is what i need in the card level :

Win Back 1 = IF([Sales Last Period WB1 60] >0 && [Sales Last Period WB1 30]<=0,1,0)


Total Win Back 1 = var tday = IF(HASONEVALUE(BIDAT[DAT_date]),LASTDATE(BIDAT[DAT_date]))
RETURN CALCULATE(SUMX(
    BICLID,
    [Win Back 1]
),    DATESINPERIOD(
        BIDAT[DAT_date],
        tday,
        -60,
        DAY))

Could you please help me on this please ?

Kind regards,

Mohammad PATEL

[v-janeyg-msft]

Hi, @mmp 

I have an idea,you can use your first measure in filter pane to filter data,then use 'countrow' function to count the number of rows in the table that meet the conditions.

Like this:

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[v-janeyg-msft]

Hi, @mmp 

It’s my pleasure to answer for you.

According to your description, you know the card visual can only return a total value.

So you may change the mark measure to column ,then add to the virtual(use summraize) table after the corresponding grouping, then you may count the number of row where the mark is 1 in that table.

Could you mind providing some sample data or field name, relationships?so we can help you.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[mmp]

Hello @v-janeyg-msft, thanks for your reply !

I finally kind find a way to solve my problem but still have some issues.

I need to get the number of people who didn't bought anything from the last 60 days :

Sales last 60 days  = 

var day_60 =DATEADD(FIRSTDATE(BIDAT[DAT_date]),-60,DAY)
RETURN

IF(MAX(BITKT[TKT_DATACHAT])>=day_60 && MAX(BITKT[TKT_DATACHAT]) <= MAX(BIDAT[DAT_date]),1,0)

Here the measure for lost customer:

wb2 = if([Sales last 60 days],0,1)

But I have a problem with my last measure that gives me the total number :

Total Win Back 2 = CALCULATE(SUMX(
    BICLID,
    [wb2] 
))

If you have any ideas how to resolve it.

Kind regards,

Mohammad

[AlB]

Hi @mmp,

Can you explain a bit more what the measure is supposed to do in the card and the expected result? Share the pbix if possible, or  samples of the tables/data model

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[mmp]

Hi @AlB ,

Thanks for your reply, i'm not able to provide any data/pbix as those are from a live connection.

I have a measure Win Back 1 that tells me if a customer has bought depending different period:

Win Back 1 = IF([Sales Last Period WB1 60] >0 && [Sales Last Period WB1 30]<=0,1,0)

So in order to filter the results I created two measures:

This one tells me if it's active or not and will allow me to filter with a disconnected table containing two rows Active/Inactive.

Statut Win Back 1 = IF([Sales Last Period WB1 60] >0 && [Sales Last Period WB1 30]<=0,"Active", "Inactive")

In this one it allows me to apply the filter on the visuals:

Filtre actif win back 1 = 
var actif = FIRSTNONBLANK(BISTATUTFILTRE[Statut],BISTATUTFILTRE[Statut]="Actif")
var inactif = LASTNONBLANK(BISTATUTFILTRE[Statut],BISTATUTFILTRE[Statut]="Inactif")
var currentState = [Statut Win Back 1]

RETURN if(currentState=actif || currentState=inactif,1,0)

So I need two visual for this:

• a list (table) with the measure "Filtre actif win back 1"=1 at the visual filter level.

• and i need a card that returns the right result, for that i use the following measure:

Total Win Back 1 = var tday = IF(HASONEVALUE(BIDAT[DAT_date]),LASTDATE(BIDAT[DAT_date]))
RETURN CALCULATE(SUMX(
    BICLID,
    [Win Back 1]
),DATESINPERIOD(
        BIDAT[DAT_date],
        tday,
        -60,
        DAY))

I am not able to filter the card with a measure i.e. use the measure "Filtre actif win back 1"=1 at the visual filter level,  the result displayed is wrong. 

Is it clearer for you ?

Mohammad