How to create a conditional column through measures ?

[Birinder]

Hi there, 

I hope you all are doing good,

Actually I am working on a Live data connected to Azure servers. Instead of importing it, I am working lively with it. But I need to create an extra field which will serve as a conditonal column. I can create a conditional column with Power Query, but for that I'll have to import the data. The datasets are pretty huge in size and exceeds the limit of threshold size. Further to add, I want to create a column, based on conditions, from tables which are all seperate and connected to each other via keys relationship. 

So how can I create a conditional column based on all the above info.

Here is a little example of what I want.

Roll 5	Roll v	EVM_ROLL	IS&BS Amount	DESIRED SOLUTION
1110	Opening Balance	R110	200000	20000
1113	Closing Balance	R2220	300000	30000

The desired solution is the column I want. If Roll 5 = 1110 and Roll v = Opening Balance and EVM_ROLL = R110 then IS&BS AMOUNT else 0

Where,

Roll 5 is from table 'Legal_entity'

Roll v is from table 'Legal Entity (d)'

EVM_ROLL is from table 'Substitution Group'

IS&BS Amount is from table 'Facts_IS_BS'

I'm badly stuck at this.

Please help me if this is possible.

Thanks and Regards.

[amitchandak]

@Birinder , You can not create a column on measures, because slicer value will not be taken by measure, so that would be like static

[Birinder]

So is there any other way ?

@AlexisOlson @Greg_Deckler 

[amitchandak]

@Birinder , a measure like

Switch( True ,
max(Table[Roll 5]) = 1110 && Max(Table[Roll v]) = "Opening Balance" && max(Table[EVM_ROLL]) = "R110", table[IS&BS AMOUNT] , 0
blank()
)

or

sumx(Summarize(Table, Table[Roll 5],Table[Roll v], Table[EVM_ROLL] , "_1", Switch( True ,
max(Table[Roll 5]) = 1110 && Max(Table[Roll v]) = "Opening Balance" && max(Table[EVM_ROLL]) = "R110", table[IS&BS AMOUNT] , 0
blank()
)), [_1])

[Birinder]

@amitchandak 

The first command is showing me error :

"
MdxScript(Model) (37, 441) Function 'SWITCH' does not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

and in the second solution,

after summarize opened bracket, "table" refers to which table? Which table name should I give here. You can answer by following the names of table I gave you above. 

Thanks for your support this far. I really appreciate the help.

[v-yiruan-msft · Accepted solution]

Hi @Birinder ，

It seems that you are connecting to Azure servers in live connection mode, in which case you cannot create calculated column, but only report-level measures. You can learn more about live connection by reviewing the following blog.

Live Connection; When Power BI comes Hybrid

You can create a table visual and put all these fields(Roll 5,Roll v,EVM_ROLL and IS&BS Amount) in the Values options of table visual. Note that you should not use any aggregation functions for the value fields(like the field IS&BS Amount) as shown below screenshot. Also, you can create a measure as follows to get the ”desired column".

DESIRED SOLUTION = 
IF (
    SELECTEDVALUE ( 'Table'[Roll 5] ) = 1110
        && SELECTEDVALUE ( 'Table'[Roll v] ) = "Opening Balance"
        && SELECTEDVALUE ( 'Table'[EVM_ROLL] ) = "R110",
    SELECTEDVALUE ( 'Table'[IS&BS Amount] ),
    0
)

Best Regards