Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have 2 tables,
Master Table. This table is used as a filter to filter out Dealer Code and name from below table.
Code |
111 |
222 |
333 |
Orders Table
Code | Dealer Code | Dealer Name |
111 | ABC | Texas |
222 | XYZ | California |
Both tables are joined in the model on Code.
Requirement: When user filters for examples like '333' as code, The Orders Table KPI shows blanks.
I want to show the string "No orders available" in the first line of the table (for dealer code and dealer name) in such scenarios.
Note: I have tried to check "Show items with no data" but that did not help
I have also tried to achieve the result using isblank function but it didn't help.
Solved! Go to Solution.
New Dealer Code =
VAR __countOrders = COUNTROWS ( OrderTable )
RETURN
IF ( __countOrders == BLANK(), "No order available", MAX ( OrderTable[DealderCode] )
)
New Dealer Name=
VAR __countOrders = COUNTROWS ( OrderTable )
RETURN
IF ( __countOrders == BLANK(), "No order available", MAX ( OrderTable[DealderName] )
)
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.
@Anonymous you can create a measure like this to override blanks
Order Count =
VAR __countOrders = COUNTROWS ( OrderTable )
RETURN
IF ( __countOrders == BLANK(), "No order available", FORMAT ( __countOrders, "General Number" )
)
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 your reply.
However, this would require me to create a new column/measure.
I wanted to show "No Orders" in the dealer name and dealer code column itself.
Try this:
Rename your Dealer Code field to Dealer Code Original.
Create a new column:
Dealer Code = IF( ISBLANK( 'Orders Table'[Dealer Code Original] ) , "No Order Available" , 'Orders Table'[Dealer Code Original] )
Then just use your new calculated Dealer Code column.
This didn't work as I already mentioned in the question
@Anonymous first, what is the issue of not creating a new measure, 2nd i'm not sure what you mean by show "no order" in place of dealer name. It's not very clear what you are looking for.
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.
In simple words, When Code 333 is selected I want my table to look like this:
Dealer Code | Dealer Name |
No Orders Available | No Orders Available |
Your calculation is creating a new column/measure
Also, do you think bookmarks can help? I mean conditionally showing another text KPI when a table is blank?
@Anonymous well then you have to create two calculations for dealer code and dealer name, or you can do this in power query and then you don't need to add these calculations. I would recommend to do in DAX rather Power Query
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 ,
Order Count = VAR __countOrders = COUNTROWS ( OrderTable ) RETURN IF ( __countOrders == BLANK(), "No order available", FORMAT ( __countOrders, "General Number" ) )
I was talking about this calculation. How does this replace the Dealer code or name is what I wanted to know.
New Dealer Code =
VAR __countOrders = COUNTROWS ( OrderTable )
RETURN
IF ( __countOrders == BLANK(), "No order available", MAX ( OrderTable[DealderCode] )
)
New Dealer Name=
VAR __countOrders = COUNTROWS ( OrderTable )
RETURN
IF ( __countOrders == BLANK(), "No order available", MAX ( OrderTable[DealderName] )
)
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.
So this worked.But my actual data has more than 1 dealer for each code.
In that case Max will not work.
I thought this will be close, but sadly this isn't working either
I think Power bI doesn't recognize that No rows in table = Blank() rows
@Anonymous I don;t know how you are visualizing and what is happening at your end. Share pbix with sample data.
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.
Im on client network. Please ping me your email
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |