Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
First off, I've reviewed the various posts by RADACAD, Enterprise DNA and SQLBI on this topic.
I have measures based on RADACAD & SQLBI all working, but its not giving me what I need/want.
I don't know why I can't wrap my head around this. All I need is basic catorgization of my data. All of the examples are based upon financial or performance analysis. I just need to be able to filter a list to say 'here are all the rows that meet the criteria of X segement'.
Specifically I have data that reprsents how often PDFs are generated from various Report Tempalates; frequency of creation.
I have measure [Count Runs] that calculates how often each report has been run over time.
I just want to dynamically drop each report (row) into a bucket to segement the data based on how often each report was run.
Can anyone point me to existing documention that may aid me? If not I can post a sample data model, etc. but was hoping to avoid that.
#FrustratingFriday
hi @rpiboy_1
have you checked these:
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
p.s posting sample data with expected result is always suggested.
@FreemanZ yes, Dax Patterns is SQLBI, though not their static segementation as I really rather have it be dynamic because I'm dealing with time.
Apologies on no sample data initially, had to run out the door to ferry a child to practice and frustrated.
Here is what I'm dealing with:
'Reports' table:
Report.ID | Report Title |
01 | Name1 |
02 | Name2 |
03 | Name3 |
04 | Name4 |
'Report Creation' table (related to Reports table by Report.ID):
Run.ID | Report.ID | Date |
01 | 02 | xx/mm/yyyy |
02 | 02 | xx/mm/yyyy |
03 | 01 | xx/mm/yyyy |
04 | 03 | xx/mm/yyyy |
05 | 01 | xx/mm/yyyy |
06 | 02 | xx/mm/yyyy |
07 | 03 | xx/mm/yyyy |
08 | 03 | xx/mm/yyyy |
09 | 01 | xx/mm/yyyy |
10 | 02 | xx/mm/yyyy |
There is also a Date Table, set-up properly.
I've defined the following segement table:
'Run Segements':
SegementName | Min | Max |
Low | 0 | 10 |
Mid | 10 | 75 |
High | 75 | 1,000,000 |
I'd prefer to have the High Max as 'null' but all of the examples use finite segements, so I just went with a number high enough that it would never be reached.
I have a measure:
'Count Report Runs' =: COUNT('Report Creation'[Run.ID]) + 0
A measure was required because there are some reports which have never been run (hence the analysis effort!) so I need to be able to return a 0 (since you can't count rows that don't exsist).
Using my sample data, we can see that we get the following count results for each report
Report | Count | 'fake' count (See note below) |
Name1 | 3 | 23 |
Name2 | 4 | 78 |
Name3 | 3 | 8 |
Name4 | 0 | 0 |
Now, let's pretend we have a lot more data, and it looks like the 3rd column.
In which case what I would like to be able to return is a visual that illustrates this:
Report | Segment Name |
Name1 | Mid |
Name2 | High |
Name3 | Low |
Name4 | Low |
Or a matrix that might look like this (extrapolating run data):
Report | CY20 | CY21 | CY22 |
Name1 | Low | Mid | Mid |
Name2 | High | Mid | High |
Name3 | Low | Mid | Low |
Name4 | Mid | Low | Low |
hi @rpiboy_1
try like:
@FreemanZ thanks, that seems to work in a limited manner, now I'll have to compare it to what I was trying to do so I can understand it.
However, I can't seem to filter the table visual showing Report Name & Segement name so that I'm only looking at a list of Reports that are in the mid segement for instance... Any thoughts?
Thanks!
hi @rpiboy_1
can you try to provide some usable sample data with expected result?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
28 | |
23 | |
12 | |
11 | |
10 |