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
Anonymous
Not applicable

Using a filter from 1 table to show blank values in another table

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

CodeDealer CodeDealer Name
111ABCTexas
222XYZCalifornia

 

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.

 

1 ACCEPTED 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.

View solution in original post

13 REPLIES 13
parry2k
Super User
Super User

@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.

Anonymous
Not applicable

@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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

@parry2k 

 

In simple words, When Code 333 is selected I want my table to look like this:

 

Dealer CodeDealer Name
No Orders AvailableNo 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.

Anonymous
Not applicable

@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.

Anonymous
Not applicable

So this worked.But my actual data has more than 1 dealer for each code.

In that case Max will not work. 

Anonymous
Not applicable

 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.

Anonymous
Not applicable

Im on client network. Please ping me your email 

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.