topic List customers with a visit outside of selected dates in DAX Commands and Tips

List customers with a visit outside of selected dates

Syndicate_Admin — Wed, 12 Jun 2024 11:35:09 GMT

I have the date filter

I have the measure that verifies if a customer has had a visit in that range, this is fine.

Seguimiento_Cliente = CALCULATE(COUNT(MovInteracciones_sql[Interaction Template Code]),MovInteracciones_sql[Interaction Template Code]="SEG_CLIENT")

Now I would like to know which client has visited but outside those dates.

I've tried the following but it doesn't quite work for me.

# Cli Sin Visitas en periodo = 
VAR ClientesPrimeraVisita = 
ADDCOLUMNS(
    VALUES(MovInteracciones_sql[Contact No_]),
    "UtimaVisita",
    CALCULATE(
        MAX(MovInteracciones_sql[Date]),
        ALL(Fechas),MovInteracciones_sql[Interaction Template Code]="SEG_CLIENT"
    )
)
VAR ClientesVisita = 
FILTER(
    ClientesPrimeraVisita,
    NOT [UtimaVisita] IN VALUES(Fechas[Date]) && NOT(ISBLANK([UtimaVisita]))
)
VAR Resultado = COUNTROWS(ClientesVisita)
RETURN Resultado

Can anyone help me?

Thank you.

Re: List customers with a visit outside of selected dates

lbendlin — Thu, 13 Jun 2024 00:46:10 GMT

If that date column is connected to your fact table then it will not be possible. You will need to feed the slicer from a disconnected table.

Re: List customers with a visit outside of selected dates

apenaranda — Thu, 13 Jun 2024 06:04:13 GMT

The table where the interactions are and know if there is a visit or not has a column also dated that is connected to the dates.

I don't quite understand what you're saying about the column date this connected to table facts. And I don't know what you mean by powering a "cutter" from a disconnected board.

Thank you

Re: List customers with a visit outside of selected dates

lbendlin — Thu, 13 Jun 2024 11:50:59 GMT

Ah, the joys of translation... "slicer" means a filter visual

"board" means table (in your data model)