dynamic funnel

[Vasek00005]

Hi,

I have table with DATE, CLIENT_ID, VISIT_ID and DEPARTMENT (and bunch of other columns). One client can make multiple visits in multiple departments. I have a measures [client_count] = DISTINCTCOUNT(CLIENT_ID) and [visit_count] = COUNT(VISIT_ID). I would like to divede clients in catagories and show it in funnel. Categories are: 1 visit, 2-3 visits, 4-6 visity. 7+ visits. And I would like these categories to by dynamic and work as slicer to filter other visuals.

I tried to make 4 different metrics, one for each category . The values are perfect, the funnel reacts on other visuals, but other visuals do not react on funnel. I would like to select category 2-3 and see which departmens are connected to these visits.

I also tried to make a lookup table of categories and put it as a category to funnel and measure [client_count] as funnel's value.  The relationship was through counted DAX column, that counted visits for each client. I thought that a counted DAX column will be re-counted every time the context would change, but not. I just hold count of ALL visits. Then the other visualisation reacts on funnel, but number of clients do not correspond of the context.

Any idea, please?

Many thanks,

Vašek 

[v-yangliu-msft]

Hi  @Vasek00005 ,

I created some data:

Whether or not there is no category field in the other visual causes no effect, you can consider using the same table field, or create the same column in another table to create the relationship.

Here are the steps you can follow：

1. Create calculated column.

Test =
    COUNTX(
    FILTER(ALL('Table'),
    'Table'[Client_ID]=EARLIER('Table'[Client_ID])),[Department])

Categories =
SWITCH(
    TRUE(),
[Test]=1,
"1 visit",
[Test]>=2&&[Test]<=3,
"2-3 visits",
[Test]>=4&&[Test]<=6,
"4-6 visits",
[Test]>6,
"7+ visits")

2. Create measure.

Value =
CALCULATE(
    DISTINCTCOUNT('Table'[Client_ID]),
    FILTER(ALL('Table'),
        'Table'[Categories]=MAX('Table'[Categories])))

3. Result:

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[Vasek00005]

Well, I placed the question to ChatGPT as well and it came up with a working solution 🙂 I tried that for two days without success. But whey I tried hard to explain the problem to you - people - then I was able to better articulate the problem. And ChatGPT understood that.

In our office we call this approch a "debug duck". You just talk to somebody who pretends to listen and finally you come with the solution yourself 🙂

And the solution here was:

To achieve your desired outcome, where you can dynamically categorize clients based on their visit counts and use these categories as a slicer to filter other visuals, you can follow these steps:

1. Create a new table for categories:

   • Create a new table in Power BI that contains the categories you mentioned: "1 visit", "2-3 visits", "4-6 visits", and "7+ visits".
   • You can manually create this table in Power BI or import it from your data source.

2. Define relationships:

   • Establish relationships between your main data table (containing DATE, CLIENT_ID, VISIT_ID, DEPARTMENT, etc.) and the new category table you created. The relationship should be based on the visit count.

3. Create measures for client counts in each category:

   • Create measures in Power BI for counting clients in each category. These measures should use the COUNTROWS and FILTER functions to count distinct clients based on their visit counts. For example:
     DAXCopy code

     Clients_1_Visit = CALCULATE( DISTINCTCOUNT('YourTable'[CLIENT_ID]), 'YourTable'[Visit Count] = 1 )
     Repeat this process for each category.

4. Use the new category table in the funnel visualization:

   • Use the new category table as the axis in your funnel visualization.
   • Use the measures you created (e.g., Clients_1_Visit, Clients_2_3_Visits, etc.) as the values in the funnel visualization.

5. Filter other visuals using the category slicer:

   • Place the category slicer (from the new category table) on your report canvas.
   • Use this slicer to filter other visuals on your report.

By following these steps, you should be able to dynamically categorize clients based on their visit counts, use these categories as a slicer to filter other visuals, and ensure that the funnel visualization reacts to changes in the slicer selection. Make sure your relationships are correctly defined and that your measures are accurately counting clients based on their visit counts.