The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi,
I am generating a custom filter based on my column values Sales and Units. So for this I am using IF statement to select between Sales and Units. Now it is working fine if the user selects either Sales or Units to filter but I am not getting any value if the user does not selects any of the 2. What I want is that the graph should display data for both Sales and Units if the user selects nothing in the filter. Below is the code and dummy table:
DisplayValue = IF( HASONEVALUE ( DisplayBy[DisplayBy] ), IF( VALUES( DisplayBy[DisplayBy] ) = "Sales", SUM( Table1[total Sales] ), SUM (Table1[Total Units] ) ), "Please select only one measure to display from the slicer" )
So instead of "Please select only one measure to display from the slicer" I need to display data for both Sales and Units based on years.
Also I can not unpivot the data as the tables are dynamic tables that are generated on different logics. So combining them and then using is not an option.
Thanks in advance.
Solved! Go to Solution.
Here are revised formula:
Display Quantity = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Qty"), SUM(Sales[Quantity]), BLANK()) Display Revenue = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Revenue"), SUM(Sales[Revenue]), BLANK())
I used by own data coloumn, you can change it to your own. I tested at my end it works, if need with your own columns, i will do it for you.
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 hope this will work:
Display Quantity = if(contains('Show Data By','Show Data By'[Show Data],"Qty"), SUM(Sales[Quantity]), BLANK()) Display Revenue = if(contains('Show Data By','Show Data By'[Show Data],"Revenue"), SUM(Sales[Revenue]), BLANK())
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.
Does it make sense to create two calculations one for sales and one for units and drop both of those on the report.
DisplaySalesValue = IF( VALUES( DisplayBy[DisplayBy] ) = "Sales", SUM( Table1[total Sales] ), BLANK()) )
DisplayUnitValue = IF( VALUES( DisplayBy[DisplayBy] ) = "Unit", SUM( Table1[total Unit] ), BLANK()) )
I believe this will work.
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.
Using this is giving me an error that "A table of multiple values was supplied where a single value was expected".
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.
Here are revised formula:
Display Quantity = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Qty"), SUM(Sales[Quantity]), BLANK()) Display Revenue = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Revenue"), SUM(Sales[Revenue]), BLANK())
I used by own data coloumn, you can change it to your own. I tested at my end it works, if need with your own columns, i will do it for you.
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.
The solution is working fine. thansk a lot @parry2k for helping me out with this.
Glad to hear and feel free to reach out to community for more questions.
Cheers!
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.
Need one last suggestion. Just now what we did was that if user was not selecting any value in the filter. What changes should I make in my code that if user selects both of the filters I should gata data for both sales and units? Right now if I select both Sales and Units in my code it is giving me the same error.
Thanks in advance.
Regards,
Siddhant
I hope this will work:
Display Quantity = if(contains('Show Data By','Show Data By'[Show Data],"Qty"), SUM(Sales[Quantity]), BLANK()) Display Revenue = if(contains('Show Data By','Show Data By'[Show Data],"Revenue"), SUM(Sales[Revenue]), BLANK())
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.
It is working completely fine. Thanks again
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |