cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leharkapil Member
Member

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

Accepted Solutions
Super User
Super User

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

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] )
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

13 REPLIES 13
Super User
Super User

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

@leharkapil 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" )
)

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





leharkapil Member
Member

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

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

 

 

Super User
Super User

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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





CoreyP Member
Member

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

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.

leharkapil Member
Member

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

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

Super User
Super User

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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





leharkapil Member
Member

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

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

leharkapil Member
Member

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

This didn't work as I already mentioned in the question

Super User
Super User

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

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] )
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)