Create IF/THEN statement in DAX using parameter

[Anonymous]

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 created a parameter for Option A and Option B, and will have a slicer referencing this parameter in my VIZ

I have tried several different ways to do this:  Using SWITCH(TRUE()), using Calculate(Filter)).  The parameter does not seem to come up as an available reference in my statement.

Appreciate any feedback or suggestions from this community.

[Anonymous · Accepted solution]

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]

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

[Anonymous]

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]

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

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]

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

[Anonymous]

Unfortunately it is not working as expected.  

The statement says if the slicer is set to "Include", then return Field1.  Else return Field2.

Thus the default value here is Field2.  That is the result I am getting, no matter what value I select in the slicer.