Forum Discussion

amikm's avatar
amikm
Helper V
5 years ago
Solved

issue with IF in DAX while creating calculated column

I am trying to write a DAX for creating a calculated column.

If the user Selects Y from CoreFlag it should only show Products like Bike, Car, Scooty, and Helicopter. And, If the user selects CoreFlag as N then he should see all Products Bike, Car, Scooty, Cycle, Scooter, and Helicopter.

 

But, the DAX that I wrote is only giving 2 products If I select N from Core Flag instead of Showing All products

I want to create a slicer on my report, so that If the user Select Y, he should see Core Products else he should see All Products

 



Note: I can achieve this using Slicer property Select all, but I want to do this with DAX

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi amikm 

    Based on your discription, you want to select all by Dax expression, please try this

    create an independent table, then drag the column Flag in this table into slicer,

    create the measure, and drag the measure into the filter on this visual, and apply filter

     

    filteronVisual = 
var _selFlag=SELECTEDVALUE(flag[Flag])
return 
if(_selFlag=="Y",IF(SELECTEDVALUE('Table'[CoreFlag])=="Y",1,0),1)

     

    result:

    See sample file attached bellow.

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    amikm , create an independent table with value Y and N

    then try like

    measure =
    var _tab = if(isfiltered(flag[Flag]) && selectedvalue(flag[Flag]) = "N", all(flag[Flag]), allselected(flag[Flag]))
    return
    calculate(countrows(filter(Table, Table[core flag] in _tab)))

     

    You can use it on the core flag, you might not separate value , refer

    Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi amikm 

    Based on your discription, you want to select all by Dax expression, please try this

    create an independent table, then drag the column Flag in this table into slicer,

    create the measure, and drag the measure into the filter on this visual, and apply filter

     

    filteronVisual = 
var _selFlag=SELECTEDVALUE(flag[Flag])
return 
if(_selFlag=="Y",IF(SELECTEDVALUE('Table'[CoreFlag])=="Y",1,0),1)

     

    result:

    See sample file attached bellow.

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

    • amikm's avatar
      amikm
      Helper V
      5 years ago

      Thanks, v-xiaotang, your example is working fine, We have a similar requirement in my project, Even it is working fine in the case of table visual, but when I switch my visual to matrix visual, I am getting no values in case of "Y", but If I select "N", I can see all the values (filteronvisual=1).

       

      Not sure, why it is behaving like this in my project, In my project, I am product as a column header in matrix visual and Location as a row. But In the case of "Y", Location and product both are showing blank.

       

      Even It's not showing different data as per slicer selection ("Y" and "N"), something wrong with the above dax in the case of matrix visual, it's only working with table visual

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        5 years ago

        Hi amikm 

        I've tested with matrix in my sample and it worked OK as also. So could you share your PBI file after removing sensitive information, so we can work on the problem you said further.

         

        Best Regards,

        Community Support Team _ Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.