How to Calculate values for inactive relationship with top N and calculate without Unpivoting

user_16672007 0
Viewed 118

I have two data bases.One contain carder and other one contain data.What I want is once I select the designation from the slicer.Then select the the employee and need to show higher defect rate styles.I have set little slicer to select higher 1 ,2 ,3 defect rate styles. .My measures as below.

Total Check = SUM(Records[Check Qty])
Total Defects = SUM(Records[Defects])
Selected_Top_N = SELECTEDVALUE('Top N'[Column1])
Defect pct = CALCULATE((DIVIDE([Total Defects],[Total Check])),TOPN([Selected_Top_N],ALL(Records[Style]),DIVIDE([Total Defects],[Total Check]),DESC),VALUES(Records[Style]))

This "Defect pct" measure works fine for Executive grade. Because it has active relationship. For qc it shows blank. My question is how to modify "Defect pct" measure using "userrelationship" or any other dax function to see top styles once I clicked qc from designation slicer and qc from the employee list.Without unpivoting I can get check qty and defect qty like below with %.by selected value for designation.'code'

Selected_designation = SELECTEDVALUE(Carder[Desingation])
SWITCH(true(),[selected_designation] ="Executive",DIVIDE([Total Defects],[Total Check]),
                            [selected_designation] ="QC",CALCULATE(DIVIDE([Total Defects],[Total Check]),USERELATIONSHIP(Records[QC],Carder[EMPLOYEE])))

'then using switch function I get these with two calulations.one for the direct relation ship.and other calculation with userrelationship for inactive one.I want rank this % with topN.End result must be once I click excetive I want rank higher defect % pct according to the selected executive and once I click qc I want the same.

[1]: https://i.stack.imgur.com/3l57W.jpg

dax powerbi-desktop powerbi
1 Answers

You need first to convert your fact table into this shape using unpivoting the last 2 columns.

Like This:

FRS

Then Create a designation Table consisting of 2 columns: Like This:

Designation

Your Final Model View should appear like this:

Karen_image

(Updated) DAX Code For Defect pct:

Defect pct =
VAR selection =
    SELECTEDVALUE ( Carder[Desingation] )
VAR conditional_selection =
    SWITCH (
        selection,
        "Executive", CALCULATE ( MAXX ( Records, DIVIDE ( [Total Defects], [Total Check] ) ) ),
        "QC",
            CALCULATE (
                MAXX ( Records, DIVIDE ( [Total Defects], [Total Check] ) ),
                USERELATIONSHIP ( Records[QC], Carder[EMPLOYEE] )
            )
    )
RETURN
    conditional_selection
user_194690880
Related