Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Juramirez
Resolver I
Resolver I

IF IS FILTERED show column a when is true or b when is false

Hi all!

 

I'm using a slicer that when it's selected, my measure changes the value to "1"and when it isn't it is by default "0". What I want to do is in a CARD visual, when my slicer is filtered it shows first value on column A and when it is not filtered it shows first value on column B.

  • In my slicer the values comes from Table[ColumnA]
  • My first measure is this and it works (This checks if my slicer was filtered or not):
    • isFiltered = IF(ISFILTERED(Table[ColumnA]),"1","0")
  • Then, the Added Column that uses previous measure goes like this but is not working (this measure must display values on Card Visual):
    • ValueShownOnCardVisual= IF([isFiltered]="1",Table[ColumnA],Table[ColumnB])

Any help is appreciated



1 ACCEPTED SOLUTION

didn't had chance to test but try this:

 

ValueShownOnCardVisual as Measure
= IF([IsFiltered]="1", FIRSTNONBLANK(Table[ColA], 1), FIRSTNONBLANK(Table[ColB]), 1))


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

22 REPLIES 22
parry2k
Super User
Super User

Try this, add new measure

 

ValueShownOnCardVisual as Measure
= FIRSTNONBLANK(IF([IsFiltered]="1", Table[ColA], Table[ColB]), 1)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k 


I forgot to say this but the ShowOnCardValue is an added Column because when i create this as a measure, I can't use mi Column A and Column B as value to the IF function. I've jus did what you said (as an added column) and it sends this error: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

 

Regards

Question:

 

Isfiltered -> Did you added it as a measure or column? It need to be measure.

What I sent you, you need to add it is a measure? 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k

 

IsFiltered is a Measure

What you sent me must be an Added Column because as a measure I can't use Table[ColumnA] ant Table[ColumnB] as values for the conditional IF.

Also, what you send shows this error: 

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

 

Regards

What I sent you need to be added it is a measure? Did you tried adding it a measure?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes, i tried to do that but it sends me the same error:

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression

Also the name of my Columns appears in gray which means that are not referenced to be used as values.

didn't had chance to test but try this:

 

ValueShownOnCardVisual as Measure
= IF([IsFiltered]="1", FIRSTNONBLANK(Table[ColA], 1), FIRSTNONBLANK(Table[ColB]), 1))


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

You save my day @parry2k!

 

Thanks, it works as i wanted!

Glad to hear. Sorry I just was thinking straight when I sent you the measure first time, would have saved lot of back and forth, I guess all good at the end 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k. I hope you could help. I'm facing the similar problem with the difference that I need to list all values not just first or last from [ColA] or [ColB]. I have a table that I would like to show dimensions from [ColA] when [IsFiltered]="1" or [ColB] when [IsFiltered]="0"

@ararat07 how does your table looks like? What results you expect?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k thanks for quick reply. I have a table in the report that has two columns: KPI and Actual. I would like to make KPI column dynamic. If the country is selected, which makes Country Filter = 1 the KPI column in the table should present Country KPI dimension otherwise Corporate KPI.

 

Country.JPGKPI.JPG

try this, add new measure called "KPI Measure"

 

KPI Measure

= IF([Country Filter]=1, MAX(Table[Country KPI]), MAX(Table[KPI]))


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I get only one value as a result. It is "Standard Cost of Goods Sold" when the country is selected and "Stock Option Expense" when the country is unchecked.

are you dropping the actual value as well in the table.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes, I do and  Actual gives me the total of all KPIs.

sorry @ararat07 can you actually share the screen shot what you are getting. I understand why you are getting that but want to see your table visual.

 

Also wehre it "actual" value, in different table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I Appreciate your efforts. The Actual column is a calculated measure sourcing from the fctPL table. There is a relationship between fctPL and tblKPI tables.

Here are the DAX formulas used:

 

Country KPI = IF('tblKPI'[Index]> 35, BLANK(), 'tblKPI'[Corporate KPI])

Country Filter = IF( ISFILTERED(dimCountries[Country]), 1, 0)

KPI Measure = IF([Country Filter]=1, MAX(tblKPI[Country KPI]), MAX(tblKPI[Corporate KPI]))

 

image.pngimage.png

 

 

throw sample data from these two tables in excel sheet and send it over.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  I shared pbix file here. Thanks again for looking at this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.