Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Any help is appreciated
Solved! Go to 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.
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.
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.
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.
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]))
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |