Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |