Need Help with Dax

[cshashank]

Hi, I have a below table

Date	Test Name	Unique Cases Executed	Pass
13-Jul-17	Cup	15	5
14-Jul-17	Cup	20	10
15-Jul-17	Tea	22	4
16-Jul-17	Cup	40	30
17-Jul-17	Cup	12	11

I want to create a report, where Pass% always shows Sum of all 'Unique Cases Executed' irrespective what date is selected.

Currently, if I apply date filter, the pass% changes based on 'Unique Cases Executed' between selected date range. How can I calculate static 'Unique Cases Executed'? Any help is appreciated. Sample of PowerBI report link: https://app.powerbi.com/view?r=eyJrIjoiZDIzNTViNmMtNjk0YS00YWZiLWE1ZDctYzNkN2Q3OWNlNDFhIiwidCI6IjhmM...

[TomMartens · Accepted solution]

Hey,

Basically you can achieve what you on two ways:

• change the interaction between the time slicer and your kpi visual
• calculate a independent measure like this
  mspassindependent = calculate(
  sum('tablename'[measurename],
  all('tablename'[nameofthedatecolumn])
  )

[TomMartens · Accepted solution]

Please provide the numbers you want to use to calculate the kpi and the result you expect

[TomMartens · Accepted solution]

Hey,

Basically you can achieve what you on two ways:

• change the interaction between the time slicer and your kpi visual
• calculate a independent measure like this
  mspassindependent = calculate(
  sum('tablename'[measurename],
  all('tablename'[nameofthedatecolumn])
  )

[cshashank]

Thanks Tom for quick reply.

Changing interaction between the time slicer and kpi visual will not work, because I need the sum of 'UniqueCasesExecuted' 

Tried with DAX you mentioned, but its giving me same result 

[TomMartens · Accepted solution]

Please provide the numbers you want to use to calculate the kpi and the result you expect

[cshashank]

In the above pic.. Last column ( yellow ) gives us Pass Percentage..

79% is coming because it calculates = 41 (pass) / 52 (Unique Cases Executed ) - both in blue

But the expected value is :

37%  based on calculation 41(Pass) / 109 (Total Cases Executed ).

[kcantor · Accepted solution]

Wrap it in calculate and remove the date filter for total unique cases executed.

Total Unique Cases Executed = CALCULATE([Unique Cases Executed], ALL('Date'[Date]))

That removes the date filter. If you need to reapply the date filter to maintain a certain year or month you can add that back in calculate.

 Then use the Total Unique Cases Executed to calculate your percentage instead.

You will need a date table. I am assuming you have one and it is called Date in my measure example.

[cshashank]

Thanks Kcantor,  TomMartens for the solution.

The mistake I did while trying TomMartens solutions was to use Sheet1[Date].[Date] instead of Sheet1[Date]

Thank you for your time.