Customers visited and not visited

DAX DAX Calculations
1

Visits.xlsx (191.0 KB)

2

Do you have a question? All I see is a link to a file.

You’ll have to outline in detail within the forum post your requirements to get support on it.

Chrs
Sam

3

Hi Sam, had posted the problem but don’t know why reason did not start, I put it back. Excuse me. I’m new to the Forum, perhaps did not do it well.

Hello, I have two tables, one with customers and the date that have been visited, and the other is a calendar table. I need to show a table with customers who have been visited and another table with those who have not been visited within a range of dates taken from a slicers. I have developed two measures:

Customer visit =
VAR minDate = MIN(DimDate[Date])
VAR MaxDate = MAX(DimDate[Date])
RETURN
CALCULATE( DISTINCTCOUNT(Visits[Name]);
FILTER( ALL(DimDate);
DimDate[Date] >= minDate && DimDate[Date] <= MaxDate
)
)

This works well.

and

Customer no visit =
VAR minDate = MIN(DimDate[Date])
VAR MaxDate = MAX(DimDate[Date])
RETURN
COUNTROWS(
FILTER(ALL(Visits[Name]);
CALCULATE( DISTINCTCOUNT(Visits[Name]);
FILTER( ALL(DimDate);
DimDate[Date] >= minDate && DimDate[Date] <= MaxDate
)
)=0
)
)

This shows well the total number of customers not visited, but not I can show in a table who they are. I have attached the file.

I appreciate the help.

4

Side note, please review here for how to post formulas into the forum

https://forum.enterprisedna.co/t/how-place-dax-formula-into-forum-topics-posts/156/3

5

Another thing to note here is formula formatting. This is really important and it doesn’t seem that you have any indentation at all.

It may seem like a small thing but makes a big different to learning DAX well. Here’s videos to review.

http://portal.enterprisedna.co/courses/108877/lectures/1604690

6

Also I don’t really use Powerpivot and would prefer Power BI desktop files if possible if that is the sort of assistance required.

I think this is easier than you think.

You don’t really need the FILTER part of your formula because the natural context from your slicer already doesn’t that job for you.

Here’s some formula ideas you can use to show tables with these type of results

First here’s the results

image
image.png1114×541 31 KB

Then formulas

Visits =
DISTINCTCOUNT( Sales[Customer ID] )

Non Visits = 
VAR NonVisitor =  MINX( ALL( Customer ), IF( ISBLANK( [Visits] ), 1, BLANK() ) )
VAR TotalNonVisits = COUNTX( ALL( Customer ), IF( ISBLANK( [Visits] ), 1, BLANK() ) )

RETURN
IF( HASONEVALUE( Customer[Customer Name] ),
    NonVisitor,
        TotalNonVisits )


All Customers = 
IF( HASONEVALUE( Customer[Customer Name] ),
    1,
    CALCULATE( DISTINCTCOUNT( Sales[Customer ID] ), ALL( Sales ) ) )
7

Thanks a lot. Excellent!!!