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.
Hello,
Is it possible to use a measure's output as a filter? In my model, depending on how many sales an employee has of a certain product in a specified time period determines the type of sales rep that the employee is, so this is why I have to use a measure to calculate what type of sales rep the employee is.
I have attached a link to download the PBIX file that describes the end goal in more detail with dummy data.
https://github.com/Zar117/Power-BI-Rank-Based-On-Switch-Measure.git
I am open to any solution.
The switch statement that I wish to rank on:
Below is a screen shot of the desired end result.
I really appreciate any help, thanks in advance.
- Zar
@EnterpriseDNA
Solved! Go to Solution.
Here is an approach that creates a virtual table and then filters it to employees of the same type (in the selected context), and then the rank is determined using the Value expression in the RANKX formula. Please try this measure expression. I'm not sure why it doesn't match your rank values, but it does calculate rank within each type group. You can adapt it to get your desired result. Did you rank based on Employee ID?
Rank by Type =
VAR currenttype = [Sales Employee Type]
VAR overallrank = [Overall Rank by Employee ID]
VAR summary =
ADDCOLUMNS (
ALLSELECTED ( Employees[Employee ID] ),
"@type", [Sales Employee Type],
"@rank", [Overall Rank by Employee ID]
)
RETURN
RANKX ( FILTER ( summary, [@type] = currenttype ), [@rank], overallrank, ASC )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Anonymous , In case you need to create Slicer on Measure to filter, you have to follow a segmentation approach.
If you want to filter in filter clause, then you have to force values to row context
Rank column
City Rank = RANKX(all(Geography[City]),[Sales])
filter
Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )
For filter approach
You need have independent table and create new measures with values to make the filter work
refer
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
If you looking for measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://www.youtube.com/watch?v=vlnx7QUVYME
@Anonymous - So yes, you can use a measure's output as a filter, as with the Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
I'll try to take a deeper look but this may help in the interim:
https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452
Or possibly this: Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
Hi Greg,
Thank you for looking at it. This issue has been been giving me a headache for some time now.
- Zar
@Greg_Deckler
Hi @Anonymous ,
You can create a calculated column and a measure to achieve it:
Rank = RANKX(ALLSELECTED('Employees'),CALCULATE([Sales Employee Type]),,ASC,Dense)
Rank by Sales Employee Type = RANKX(FILTER(ALLSELECTED('Employees'),'Employees'[Rank]=MAX('Employees'[Rank])),[Total Sales],,ASC,Dense)
Best Regards
Rena
Hi @v-yiruan-msft ,
Thank you for your reply. This is a good solution, however becuase calculated columns only update at refresh time, using one will not solve the issue in my case.
I need the subcategory, 'Sale Employee Type' to be calculated based on the Start and End Date slicer selection, and then the employees to be rank. Do you know of a work around that does not require the use of a calculated column?
Thank you,
Hi @Anonymous ,
I'm so sorry I didn't find other alternative solution to create a measure that sort the data base on the measure [Sales Employee Type] currently...
@mahoneypat , @Ashish_Mathur Could you please help check the following problem? You can download the sample pbix file from the this link. Thank you in advance.
Best Regards
Rena
Here is an approach that creates a virtual table and then filters it to employees of the same type (in the selected context), and then the rank is determined using the Value expression in the RANKX formula. Please try this measure expression. I'm not sure why it doesn't match your rank values, but it does calculate rank within each type group. You can adapt it to get your desired result. Did you rank based on Employee ID?
Rank by Type =
VAR currenttype = [Sales Employee Type]
VAR overallrank = [Overall Rank by Employee ID]
VAR summary =
ADDCOLUMNS (
ALLSELECTED ( Employees[Employee ID] ),
"@type", [Sales Employee Type],
"@rank", [Overall Rank by Employee ID]
)
RETURN
RANKX ( FILTER ( summary, [@type] = currenttype ), [@rank], overallrank, ASC )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |