cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Card Visual to return value matching slicer selection

Hello everyone,

 

Here  is my sample data. Please only consider 2 sheets "Contact" and "General Info" (my 2 tables in Power BI).

 

I have a slicer created from 'General Info'[Store Name].

 

Assuming a random Store has been selected from that slicer, I'd like to create some measures that can fill in/answer the information to every "x" from the table below.

 

Each x represents a Card Visual.

 

Expected Result:

  • If Store A is selected, show the available name and phone number of Store A's Primary Customer Service, Secondary Customer Service, and General Manager respectively. If name or phone number is blank, return "N/A"
  • If no Store is selected, return Blank 
 NamePhone
Primary Customer ServiceEmily115
Secondary Customer ServiceN/AN/A
General ManagerMatthew115-8

 

Is there a way to create such Measures to put in Card visuals? Please can anyone advise? Thank you so so much!!!!!

 

 

Update on answer:

 

The answer is the combination between this calculated column 

Phone Number = CALCULATE(FIRSTNONBLANK(Contact[Phone], Contact[Phone]), ALLEXCEPT(Contact, Contact[Store], Contact[Name]))

and this measure:

Secondary Phone = 
VAR temp = CALCULATE(MAX(Contact[Phone Number]), FILTER(Contact, Contact[Contact Type] = "Secondary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Card Visual to return value matching slicer selection

@trdoan ,

 

Create 6 measures using DAX as below:

Primary Name = 
VAR temp = CALCULATE(MAX(Contact[Name]), FILTER(Contact, Contact[Contact Type] = "Primary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Primary Phone = 
VAR temp = CALCULATE(MAX(Contact[Phone]), FILTER(Contact, Contact[Contact Type] = "Primary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Secondary Name = 
VAR temp = CALCULATE(MAX(Contact[Name]), FILTER(Contact, Contact[Contact Type] = "Secondary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Secondary Phone = 
VAR temp = CALCULATE(MAX(Contact[Phone]), FILTER(Contact, Contact[Contact Type] = "Secondary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

General Manager Name = 
VAR temp = CALCULATE(MAX(Contact[Name]), FILTER(Contact, Contact[Contact Type] = "General Manager" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

General Manager Phone = 
VAR temp = CALCULATE(MAX(Contact[Phone]), FILTER(Contact, Contact[Contact Type] = "General Manager" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Capture.PNG 

 

You may refer to the appendix.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Highlighted
Super User I
Super User I

Re: Card Visual to return value matching slicer selection

Your 'Each x represents a Card Visual.' table didn't show up.

 

Looking at the stated requirements, can't you use a multi-row card to display the contact details?  As long as you have the relationship set up correctly, the slicer selects the store and the card displays the details.

Highlighted
Helper III
Helper III

Re: Card Visual to return value matching slicer selection

Hi @HotChilli  there is no table that is "Each x represents a Card visual" from my data. I merely tried to show what my expected result is supposed to be. 

 

I have hundred of contact types and names in my real data. A Card visual seems to be of a better choice for me than a multi-row card because multi-row card seems to me a clutter of information and I want to display only 1 Store's contact details at a time

 

But thanks for your suggestion.

Highlighted
Super User I
Super User I

Re: Card Visual to return value matching slicer selection

You're confusing me.

 

The multi-row card would display the contact details for only one store at a time, once the store has been selected in a separate slicer.

It's not a clutter, it works very well, almost perfectly for your requirement i think.

Highlighted
Community Support
Community Support

Re: Card Visual to return value matching slicer selection

@trdoan ,

 

Create 6 measures using DAX as below:

Primary Name = 
VAR temp = CALCULATE(MAX(Contact[Name]), FILTER(Contact, Contact[Contact Type] = "Primary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Primary Phone = 
VAR temp = CALCULATE(MAX(Contact[Phone]), FILTER(Contact, Contact[Contact Type] = "Primary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Secondary Name = 
VAR temp = CALCULATE(MAX(Contact[Name]), FILTER(Contact, Contact[Contact Type] = "Secondary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Secondary Phone = 
VAR temp = CALCULATE(MAX(Contact[Phone]), FILTER(Contact, Contact[Contact Type] = "Secondary Customer Service" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

General Manager Name = 
VAR temp = CALCULATE(MAX(Contact[Name]), FILTER(Contact, Contact[Contact Type] = "General Manager" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

General Manager Phone = 
VAR temp = CALCULATE(MAX(Contact[Phone]), FILTER(Contact, Contact[Contact Type] = "General Manager" && Contact[Store] = SELECTEDVALUE('General Info'[Column1])))
RETURN 
IF(ISBLANK(temp), "N/A", temp)

Capture.PNG 

 

You may refer to the appendix.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper III
Helper III

Re: Card Visual to return value matching slicer selection

Hi @v-yuta-msft , thanks for your help and everything worked perfectly! 

 

However, may I ask another question. I noticed in my real data there's chance where a person might hold different titles and his/her information might be left blank in any other positions but the first one. Is there a way to fill in the same kind of information provided in the first postion?

 

StoreContact TypeNamePhone
BPrimary Customer ServiceOlly547-5
BSecondary Customer ServiceOlly 

 

I have removed the phone number in the Secondary Customer Service in my Sample Data to imitate the scenario. Is there a way to get those measures you've written for me to return automatically the same phone number for all positions that Olly (or anyone) holds if they're left blank?

 

Thank you so so much!

Highlighted
Community Support
Community Support

Re: Card Visual to return value matching slicer selection

@trdoan ,

 

Just create a new calculate column like below and use the new calculate column in measures above:

Phone Number = CALCULATE(FIRSTNONBLANK(Contact[Phone], Contact[Phone]), ALLEXCEPT(Contact, Contact[Store], Contact[Name]))

Capture.PNG 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper III
Helper III

Re: Card Visual to return value matching slicer selection

Hi @v-yuta-msft , thanks again for your help! However, it seems to work in your file but not mine.

 

Here is the link to my pbi file. Please can you check what's wrong with it that the calculated column didn't give me the right result like in the pic you posted. My calculated column and yours are completely identical.

 

Thanks so muchhhh!

Highlighted
Community Support
Community Support

Re: Card Visual to return value matching slicer selection

@trdoan ,

 

Use measures:

 

Phone Number = CALCULATE(LASTNONBLANK(Contact[Phone], TRUE()), ALLEXCEPT(Contact, Contact[Store], Contact[Name]))
or
 
Phone Number = CALCULATE(MAX(Contact[Phone]), ALLEXCEPT(Contact, Contact[Store], Contact[Name]))
 instead.
 
In addtion, similar case about the reason for why FIRSTNONBLANK() can work for your reference:
 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors