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 fie...
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

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

    • Birinder's avatar
      Birinder
      Helper III
      4 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        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