Forum Discussion
Creating bar chart total visits with dynamic distinct count
Hello all,
a task that seems simple at first sight gets more complicated with every hour I spend trying, reading and thinking about it. If there is already a post about this, I didn't notice it because I guess I don't have the right words to describe it.
I have a chart with Customer_ID and Date(YYYY.MM.DD):
| Customer_ID | Date (YYYY.MM.DD) |
| 1 | 2021.01.01 |
| 2 | 2021.01.02 |
| 3 | 2021.01.03 |
| 1 | 2021.01.04 |
| 1 | 2021.01.05 |
| 2 | 2021.01.06 |
| 4 | 2021.01.07 |
| 1 | 2021.01.08 |
At the end I want to create a chart from these data by
Axis (X) = Number of visits in period X (1 visit/ 2 v. / 3 v. / etc.)
Values = Number of unique customers in period X
Period X = dynmaically selected
Two bar chart examples:
Period 2021.01.01 to 01.08
Period 2021.01.03 to 01.08
Best
Andreas
Customers Count : =
VAR _currentvisitcount =
MAX ( 'Visit Count'[Visit Count] )
VAR _customersvisitcount =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Customers[Customer_ID] ),
"@visitcount", CALCULATE ( COUNTROWS ( Data ) )
),
[@visitcount] = _currentvisitcount
)
RETURN
COUNTROWS ( _customersvisitcount )
4 Replies
- Jihwan_Kim
Super User
Customers Count : =
VAR _currentvisitcount =
MAX ( 'Visit Count'[Visit Count] )
VAR _customersvisitcount =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Customers[Customer_ID] ),
"@visitcount", CALCULATE ( COUNTROWS ( Data ) )
),
[@visitcount] = _currentvisitcount
)
RETURN
COUNTROWS ( _customersvisitcount ) Thank you JiHwan Kim for your quick reply.
To understand it correctly, I have to create a new table with the unique ID's before I can start with the visualization. There is no other solution?
- Jihwan_Kim
Hi,
Thank you for your feedback.
I created the dimension table for CustomerID because
- I do not know how your actual data model looks like.
- in order to make data model a bit more easy to understand.
No need to create this dimension table.
Hello Jihwan_Kim,
it works. Thank you for your help.
Best,
Andreas
