DAX Switch Help

[cottrera]

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

[cottrera]

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

[cottrera]

Hello to all responders. I manged to resolve this issue myself by rethinking and applying a more simplified solution.

[AllisonKennedy]

@cottrera Awesome! Glad you resolved this. Please can you post what you did so we can all learn? Then you can mark it as the solution.

Cheers!

[cottrera · Accepted solution]

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")

[v-lili6-msft]

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

[amitchandak]

@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.

[Greg_Deckler]

@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.