Use measure as dimension for further calculations (looking for workaround)

[SzymonP]

Hello there,

I have a fact table with Date, Customer ID, Event ID and I'd like to create a visual with number of Customers per number of Events in a given date. Dates are obviously taken from slicer, and I cannot find a way to do this within Power BI.

My para SQL mind tells me to do this:
select num_of_events, count(customer_ID) from (
    select customer_ID, count(event_ID) as num_of_events from origin_table)

and of course I could do that as a static table, but then the date slicer won't work. I'm thinking there's a solution with joining the fact table with new calculated table, but I may lack imagination on how to do that.

Any tips appreciated 🙂

[HassanAshas]

Could you share sample dataset and the sample output you are expecting from your data? 
Because I am not sure if I understand you correctly. 

If you simply want to calculate the Number of Customers per Number of Events for a given date then you can just add "Date" to a "Table Visual". And then add this measure as a second column, 

Number of Customers per Number of Events = 

DIVIDE([Number of Customers], [Number of Events], 0)

This will calculate the ratio of Number of Customers with Number of Events for every date (through Filter Context. Make sure your date is coming the same table from which Events and Customers are coming, or otherwise your Date column should have a relation with the Date in the Calendar Table. 

[SzymonP]

Thanks for the reply, though that's not what I wanted at all. Let's say this is the input data:

CustomerID	EventID	Date
ABC	1	1/1/2023
ABC	2	2/2/2023
ABC	3	4/4/2023
DEF	4	1/1/2023
DEF	5	2/2/2023
XYZ	6	4/4/2023

And now for the whole database I'd have 
3 visits = 1 
2 visits = 1
1 visit = 1

However, if I narrow it down to January only or Q2 only, then I will have:
1 visit = 2

So to speak in Power BI:
Count of Event_ID has to be a dimension that's dynamically changing based on date selection, and count if Customer_ID has to be the measure.