Filter Issue and Lookup Table

[seenai]

Hi,

a) I found the measure not calculated properly when I apply YEAR Filter.

Both the TABLES with and without filters are shown in the Power BI File available at Link Rating File Folder 20200405 (File : MW_VendorRating.pbix).

I want to apply filter without affecting calculation.

b) In the same File, I have a measure Score_QPPM. It is made using IF function. I want to get the result using table Tbl_QPPM.

Please help me for these two points, if possible.

[amitchandak]

@seenai , I checked the file

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[seenai]

@amitchandak 

Hi

Please find below my request explained in details.

[az38]

Hi @seenai 

if i understand you correct you need smth like

Score_QPPM new = 
var _QPPM = [Calc_QPPM]
var _Score = CALCULATE(FIRSTNONBLANK(Tbl_QPPM[QPPM Score],1), Tbl_QPPM[Lower bound] < _QPPM, Tbl_QPPM[Upper Bound]>=_QPPM)
RETURN
IF(ISBLANK(_Score), 25, _Score)

[seenai]

@az38 

Hi,

a) Thanks for your solution to my Request 2.👍

b) I need a solution for my Request 1 also. 

THe problem and expected result is as below : (Refer Power BI File available at Link Rating File Folder 20200405 (File : MW_VendorRating.pbix).

[az38]

@seenai 

I didn't dive deep in your calculation but try to place your statement into CALCULATE()

Calc_QPPM = CALCULATE((sum(T_MW_VR02[N.of Scraps (D)])+sum(T_MW_VR02[N.of Deviations (E)]))/sum(T_MW_VR02[Received Qty (F)])*1000000)

 

[seenai]

@az38 

Hi

I would like to explain the problem further as below.

1st Measure Defined : Calc_QPPM (No problem till now)

2nd Measure : Score_QPPM_Lookup  (This is based on measure Calc_QPPM).

Now the problem is as below : (Updated Power BI File available at LinkRating File Folder 20200405 (File : MW_VendorRating.pbix).

[v-chuncz-msft · Accepted solution]

@seenai 

You may use RELATED to add a calculated column instead. To check DAX query, take a look at Examine report elements with Performance Analyzer.