issue with IF in DAX while creating calculated column

[amikm]

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

[v-xiaotang · Accepted solution]

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]

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]

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.

[amikm]

Hi @v-xiaotang, Overall this solution is working fine, there is something wrong with my data model and it is not working for the core solution and showing all the columns in case of all solutions and visual becomes blank in case of core solutions. 

Might be its model issue, but in general, this solution looks good

[v-xiaotang]

Hi @amikm 

Thank you for your reply. if you have further questions please let me know.

Could you please mark the solution you find helpful by clicking Accept as Solution. Really appreciate!😃

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.

[amitchandak]

@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

[amikm]

Hi @amitchandak,

I am getting the below error while I tried to implement the DAX

[amikm]

@amitchandak, thanks for your reply,
But I want to use that in my slicer