Conditional Column on Live Connection

[harshagraj]

Hello all I am using a live connection(Direct Query Mode)

I have a Measure I,e

Skill Index = DIVIDE(AVERAGE('SA USER_RATING'[Final_User_Rating]),

AVERAGE('SA USER_RATING'[MANAGER_EXPECTATIONS])

,0)
Now I need a Stacked Bar to show who are Critical and Not Critical. Critical is Skill Index <=0.5. 
I tried creating a Calculated Column But I am getting an error saying On Direct Mode I can't use Average. 
Please help me what is the other way to achieve this.
I need to filter some table based on stacked bar.I,e Critical / Not Critical.

User	Final User Rating	Manager Expectation	Skill Index
ABC	2	3	0.666666667
BCD	4	2	2
EFG	1	3	0.333333333

[Icey]

Hi @harshagraj ,

Try this:

1. Enter data to create a Critical table.

2. Create a measure.

Measure =
VAR Critical_ =
    IF ( [Skill Index] <= 0.5, "Critical", "Not Critical" )
RETURN
    IF ( MAX ( Critical[Column1] ) = Critical_, [Skill Index] )

3. Create a Stacked column chart.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harshagraj]

Hi @Icey thank you for the reply! It is working but I need to achieve this like in the below image.

The above one i did it in Extract mode. There i could able to achieve in Column.
Below are the formulas i have used.

[Measure] User & Comp LOD =

CALCULATE([Skill Index],

ALLEXCEPT('SA USER_RATING','SA USER_RATING'[COMPETENCY_L1_NAME],'SA USER_RATING'[USER_DN]))

[Measure] Skill Flag_ =

var critical_=

CALCULATE(IF([User & Comp LOD]>=0 && 'SA USER_RATING'[User & Comp LOD]<=0.4,"Far",

IF([User & Comp LOD]>0.4 && [User & Comp LOD]<=0.7,"Below",

IF([User & Comp LOD]>0.7 && [User & Comp LOD]<1,"Achievers",

IF([User & Comp LOD]>=1,"Star")))),ALLEXCEPT('SA USER_RATING','SA USER_RATING'[COMPETENCY_L1_NAME],'SA USER_RATING'[USER_DN]))

Return

IF(MAX('Table'[Column1])=critical_,'SA USER_RATING'[User & Comp LOD])

LOD to be applied on Competency L1 and User_DN.

[Icey]

Hi @harshagraj ,

Sorry to reply late.

I'm afraid that my method will not meet your needs. 

Best Regards,

Icey

[amitchandak]

@harshagraj , Try like

This should work as a measure

DIVIDE(sum('SA USER_RATING'[Final_User_Rating]),sum('SA USER_RATING'[MANAGER_EXPECTATIONS]))

or

new measure =

AVERAGEX('SA USER_RATING', DIVIDE('SA USER_RATING'[Final_User_Rating],'SA USER_RATING'[MANAGER_EXPECTATIONS]))

[harshagraj]

Hi @amitchandak thank you. But I cannot use measure in Legend. I need to show critical and not critical.