Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
5 years ago
Solved

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(Repair...
Need Help
  • cottrera's avatar
    cottrera
    5 years ago

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

cottrera's avatar
cottrera
Post Prodigy
5 years ago

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)
TeamRepairs Completed (Count ROws)%
 

 

 
Team 1616.7% 
Team 21020.0% 
Team 3812.5% 
Team 4520.0% 
Team 5812.5% 
Team 61414.3% 
Team 71216.7% 
Team 81020.0% 
Team 91216.7% 

 

Table_Percentages (Static table)
Jobs%
1100.0%
250.0%
333.3%
425.0%
520.0%
616.7%
714.3%
812.5%
911.1%
1020.0%
1118.2%
1216.7%
1315.4%
1414.3%

 

Hope this explains it

cottrera's avatar
cottrera
Post Prodigy
5 years ago

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    5 years ago

    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

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    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's avatar
      cottrera
      Post Prodigy
      5 years ago

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