Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create IF/THEN statement in DAX using parameter

I have to create a dynamic field that basically says "if option A, return x.  If option B, return y"   X and Y are 2 different fields (columns) within the same record in my query.   I have create...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

    I'd like to clarify some features you misunderstand:

    Power bi contains multiple data levels:  query table(query, custom function, query parameters) -> data model(table, calculate table, calculate column) -> data view(visual, filter, measure)  

    These different levels are generated from left to right, you can use parent to effect its child but the child not able to affect the parent level.

    For this scenario, it is impossible to use data view filter/slicers to interact with query parameters or create dynamic calculate column/table based on data view filter/slicers. (query parameters can interact with query table records, it will also affect generated data model tables and data view tables)

    In summary, you can set query parameters and custom column on the query table side with if statement to dynamic change custom column value based on query parameter values or create a measure on the data view side to integrate with slicer to display dynamic result.

    Dynamic measure(DAX) :

    My Favorite DAX Feature: SELECTEDVALUE with SWITCH 

    M query(power query) :

    #"Added Custom" = Table.AddColumn(#"previous step", "Dynamic", each if Parameter= "A" then [ColumnA] else if Parameter= "B" then [ColumnB] else null)

    Regards,

    Xiaoxin Sheng

parry2k's avatar
parry2k
Super User
6 years ago

Anonymous I guess you turned on Enable Load on parameter and showing it as a slicer on the page, correct?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Yes, I Enabled Load and the slicer is present in the VIZ.  DAX does not recognize that the parameter is avaiable to reference, when I start typing the statement.

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous I don;t think using parameter is right approach for it. You should create another table with two options in it and then use that table/column for slicer and everything will fit together very well.

     

    Would appreciate Kudos 🙂 if my solution helped.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I tried that option as well.  There is no relationship between the new table (which previously was parameter) and datasource that needs to reference the new table, because the values are independent. 

       

      Example:  lets call my main datasource QRY1.  I have Field1 and Field2, both of which are type INT.   

       

      Now I have new table (previously parameter) which we will call QRY2. QRY2 has Field4, with values of "Include" and "Exclude".  

       

      So for a given record, if QRY2.Field4 = Include, then QRY1.newfield = Field1.  If QRY2.Field4 = Exclude, then newfield = QRY1.Field2.  

       

      Statement I have tried: 

      Column = SWITCH(TRUE(),'QRY2'[Field4] = "Include",'QRY1'[Field1], 'Qry2'[Field2])

       

      I have used SWITCH(TRUE()) elsewhere and it works just fine.  But here, DAX doesnt recognize the new table or parameter as an object that can be referenced. 

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous try this, yes this new table doesn't need any relationship

         

        Column = 
VAR __selectedValue = SELECTEDVALUE ( 'QRY2'[Field4] )
RETURN
IF ( __selectedValue = "Include",'QRY1'[Field1], 'Qry2'[Field2])