Count of Frequency of occurrence issue

Power BI
1

My raw data has Name, location name, Date of login. I have a virtual table which gives me location name, Frequency Group and Login Counts. Here Frequency Group and Login Count are Measures. What I really want to do is -

  1. Count How many logins per Location (Should be able to filter by year/month)
  2. Then Compare it with Frequency Group to see which bucket that count falls into
  3. Then Again count- How many Locations fall into each bucket. Meaning there are “50” locations with “1 time” logins, "20 " locations with “2-5 times logins” etc…

I did First 2 steps. I’m not able to do 3rd. Can someone help me. Here is the sample data after step 2.
Virtual data.xlsx (8.7 KB)

2

@pshetty,

Is this what you’re looking for?:

image

Total Logins Per Bin =

CALCULATE(
    [Total Logins All],
    ALLEXCEPT(
        Data,
        Data[Frequency Group]
    )
)

Total Logins All = SUM(Data[Total Login])

Hope this is helpful. Full solution file posted below.

1 Like
3

Wow! Yes, That table you have, that is exactly what I’m trying to do. But stuck with no idea to write DAX. I have ideas in other world to do this! :smile: Anyway. Thanks for the DAX. I will try this and let you know. It might be something to do with Data model too… Let me try this first.

4

@pshetty,

Great - if you have any questions/problems, just give a shout.

  • Brian
5

I’m sorry Brian, but calculation is not working with Year Slicer. It gives wrong counts. I will soon upload pbix file. I thin that will help better.

6

@pshetty,

Yes, just upload the PBIX so I can take a look at the full use case - should be straightforward to fix.

  • Brian
7

Thanks for posting your question @pshetty. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

8

Here is the file. Sorry for the delay, I had hard time replicating with dummy data.
Login data -Test - Copy.pbix (362.8 KB)

Real Problem is Connecting date with that count. Filter is not flowing correctly.

9

@pshetty,

Thanks for the PBIX file - definitely makes it easier to provide a better, clearer solution.

This is a textbook dynamic segmentation problem. There are number of different ways to handle this, but I followed the approach that @sam.mckay uses in his videos on this topic.

Step 1: create a disconnected supporting table defining the parameters of your frequency bands:

image

Step 2: create a measure to count the number of locations in each frequency band:

Dynamic Freq Count Locations = 

VAR RankingDimension = VALUES( 'Loigin Data'[Location Name] )

VAR DynGp =
COUNTROWS( 
    FILTER(
        RankingDimension,
        COUNTROWS(
            FILTER(
                'Frequency Groups',
                [Total Logins2] >= 'Frequency Groups'[Band Min] &&
                [Total Logins2] <= 'Frequency Groups'[Band Max]
            )
        ) > 0
    )
)

RETURN
DynGp

Here’s what it looks like all put together:

image
image894×530 22.1 KB

And it adjusts dynamically based on the slicer selection.

@sam.mckay has done a bunch of videos on this topic. Here are just a couple that do a great job explaining this general approach:

I hope this helpful. Full solution file posted below.

1 Like
10

This is very cool. I knew I was not doing it right. I was watching his videos, I think I haven’t got segmentation section yet… Thank you very much for taking time to explain this. I will try myself now.

11

@pshetty,

It’s a really flexible and powerful technique. Here’s one more short (11 min.) video where @sam.mckay breaks down really clearly exactly what the DAX measure is doing.

  • Brian
12

Thank you. It is just the art of using formula. I think I need to use more DAX.

13

Superb Brian. Nice work

1 Like