Forum Discussion
DAX Switch Help
Hi hopefully I explain this correctly
I have a table of completed property repairs in my report. I have used DAX to perform a simple count of these completed repairs (Repairs = COUNTROWS(Repairs_Table) my company need to post inspect a % of these repairs. The % needs to change based on the count of repairs.
I have another table which repairJobs and the % for which I would like to apply. For example if we complete 5 repairs I would like to work out 20% or if we complete 14 repairs we could like to work out
14%.3 (see table example below)
I am asuming the SWITCH function could be used?
Example of % table
| Table_Percentages | |
| Jobs | % |
| 1 | 100.0% |
| 2 | 50.0% |
| 3 | 33.3% |
| 4 | 25.0% |
| 5 | 20.0% |
| 6 | 16.7% |
| 7 | 14.3% |
| 8 | 12.5% |
| 9 | 11.1% |
| 10 | 20.0% |
| 11 | 18.2% |
| 12 | 16.7% |
| 13 | 15.4% |
| 14 | 14.3% |
thanks
Richard
Hi yes
I was over complicating things and realised that I done need to switch/ look up a choice of many differenct percentages to apply to my table. In fact I only need to apply a choice of two percentages depending on my count results. See below.
Calc Targets = IF(AND([Comp Repairs]>=1,[Comp Repairs]<=10),"0.05")&IF(AND([Comp Repairs]>=10,[Comp Repairs]<=40),"0.1")
7 Replies
cottrera Sorry, having trouble following, can you post sample data as text and expected output? Which did you post, source data or output? Whichever it is, please post the other
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.cottrera , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.Hi
I am unable to add a sample report. But will try to explain
The results table is fine appart from the % column which is what I am trying to populate.
So If we tale line one as an example 'Team1' who completed just 6 jobs. I need to go and check my static percentages table and cross reference 6 jobs to find out what % need to be added to the results column called '%'
Results Table (Changes based on dates slicer) Team Repairs Completed (Count ROws) % Team 1 6 16.7% Team 2 10 20.0% Team 3 8 12.5% Team 4 5 20.0% Team 5 8 12.5% Team 6 14 14.3% Team 7 12 16.7% Team 8 10 20.0% Team 9 12 16.7% Table_Percentages (Static table) Jobs % 1 100.0% 2 50.0% 3 33.3% 4 25.0% 5 20.0% 6 16.7% 7 14.3% 8 12.5% 9 11.1% 10 20.0% 11 18.2% 12 16.7% 13 15.4% 14 14.3% Hope this explains it
Hello to all responders. I manged to resolve this issue myself by rethinking and applying a more simplified solution.
hi cottrera
First, It's pleasant that your problem has been solved by yourself.
and for your case, I think there is another simple measure that try to use LOOKUPVALUE to get it:
Result = LOOKUPVALUE(Table_Percentages[%],Table_Percentages[Jobs],[Repairs])Regards,
Lin
