Forum Discussion

Birinder's avatar
Birinder
Helper III
4 years ago
Solved

How to create a conditional column through measures ?

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 5Roll vEVM_ROLLIS&BS AmountDESIRED SOLUTION
1110Opening BalanceR11020000020000
1113Closing BalanceR222030000030000

 

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.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        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])