Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All, I have been stuck on this for days now and I think I am closer but still do not have it figured out.
I have a visual with a list of vendors, MTTR Days value (lower is better), and count of work order.
I have created a Select Top N table with a column holding values 5, 10, 25, 50, etc...
Within this table I have the column:
Selected N = SELECTEDVALUE('Select Top N'[Select Top N])
I have also created a measure MTTR Days Top N Vendors:
MTTR Days Top N Vendors = IF([Selected N]=BLANK(),AVERAGE(data[ETTR Days]),
CALCULATE(DATA[MTTR DAYS], TOPN('Select Top N'[Selected N], data, [Work Order Vendor], ASC, data[MTTR DAYS])))
So I have my visual and my slicer list of the values I input. However, the number of vendors is not changing with the selection of a value in the slicer. It does change the values sometimes though and I do not know why?
My ultimate goal is to the entire list of vendors showing, then a user can choose to see Top 5, 10, etc, vendors with the lowest MTTR Days (lower is better). I would like to also have the user be able to choose to only look at vendors with at least X number of workorders. I have this in place with a slicer using the measure:
MTTR Slider = CALCULATE(COUNTA(data[ETTR Days]), FILTER(DATA, DATA[Work Order Vendor]=EARLIER(DATA[work order vendor])))
I ave attached a trimmed down .pbix file here: .pbix file.
The ultimate question I want a user to be able to answer is...who are the X number of vendors with the lowest MTTR days while also having at least X numberfo work orders.
Thank you in advance!
Solved! Go to Solution.
Hello @KMcCarthy9
I create a table summarizing the data table by work order provider and filter providers with MTTR NULL days as below screenshot shown, and then I use this table to get the primary N values.
Table =
FILTER (
SUMMARIZE (
data,
data[Work Order Vendor],
"MTTR Days", AVERAGE ( data[ETTR Days] ),
"WO Count", COUNT ( data[WorkOrder Number] ),
"MTTR Slider", COUNT ( data[ETTR Days] )
),
NOT ( ISBLANK ( [MTTR Days] ) )
)
And then create the following measures:
Measure MTTR Days = SUM ( 'Table'[MTTR Days] )
Measure WO Count = SUM('Table'[WO Count])
top N MTTR Days =
IF(ISBLANK([Selected N]),
[Measure MTTR Days],
CALCULATE (
[Measure MTTR Days],
TOPN ( [Selected N], ALL ( 'Table'[Work Order Vendor] ), [Measure MTTR Days], ASC ),
VALUES ( 'Table'[Work Order Vendor] )
))
top N WO Numbers = IF ( ISBLANK ( [top N MTTR Days] ), BLANK (), [Measure WO Count] )
Finally, place them in the table visual.
Best regards
Community Support Team _ Jing Zhang
If this post helps,please consider Accepting it as the solution to help other members find it.
Hello @KMcCarthy9
I create a table summarizing the data table by work order provider and filter providers with MTTR NULL days as below screenshot shown, and then I use this table to get the primary N values.
Table =
FILTER (
SUMMARIZE (
data,
data[Work Order Vendor],
"MTTR Days", AVERAGE ( data[ETTR Days] ),
"WO Count", COUNT ( data[WorkOrder Number] ),
"MTTR Slider", COUNT ( data[ETTR Days] )
),
NOT ( ISBLANK ( [MTTR Days] ) )
)
And then create the following measures:
Measure MTTR Days = SUM ( 'Table'[MTTR Days] )
Measure WO Count = SUM('Table'[WO Count])
top N MTTR Days =
IF(ISBLANK([Selected N]),
[Measure MTTR Days],
CALCULATE (
[Measure MTTR Days],
TOPN ( [Selected N], ALL ( 'Table'[Work Order Vendor] ), [Measure MTTR Days], ASC ),
VALUES ( 'Table'[Work Order Vendor] )
))
top N WO Numbers = IF ( ISBLANK ( [top N MTTR Days] ), BLANK (), [Measure WO Count] )
Finally, place them in the table visual.
Best regards
Community Support Team _ Jing Zhang
If this post helps,please consider Accepting it as the solution to help other members find it.
I tried it like this and it working.
MTTR Days Top N Vendors =
CALCULATE([MTTR DAYS], TOPN([Selected N],ALLSELECTED( data[Work Order Vendor]), data[MTTR DAYS], ASC), VALUES(data[Work Order Vendor]))
All measure on the table has to follow this approach of using top N. Or use TOP as a filter or this measure not blank as a filter
File Attached after signature
Hi @amitchandak,
I have uplaoded another file showing some of my issue. I added vendors that have NULL for MTTR Days. When selecting the TOP N, I do not want these to be included. Currently in the file, I have Top 5 selected, and it is showing me 3 vendors with NULL MTTR Days.
How do I adjust my DAX to not include these?
I really appreciate the help!
Hi @amitchandak
This is working beautifully on the test .pbix file, however my real data model has a lot more than 10 vendors and I cannot get it to work, even though everything is the same. When I choose 5, 10, 25...the visual is just blank - shows nothing at all. What am I doing wrong?
There are some vendors that have BLANK or NULL for MTTR days. I would want those excluded and only count vendors where MTTR value is at least 0.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |