Require a Slicer for Data Output

[irnm8dn]

I have userd Power BI, with reasonable success, to create a directory for my organization.  As an example, a user selects a "Market", and below I have cards showing Address, Phone, Local Staff and Corporate Contacts.

I am trying to force a rule that the the slicer where a user selects a specific market must be selected to show data on the cards below.  Is there a DAX statement that I can use that require a user to select an option from the slicer, to "reveal" the supporting details.

Thanks in adavance!

[vcastello]

Hi irnm8dn

What about ....

IF(HASONEVALUE(SlicerTable[SlicerField]), [Measure], BLANK())

Where ...

SlicerTable is the table where the slicer comes from,

SlicerField is the slicer

and

[Measure] is the result to be shown in the card

I think that's what you need

Vicente

[irnm8dn]

@vcastello

I think we are onto something.

In this case I am using two tables:

• Lat & Long
• Sheet 1

The slicer is "Market"

The Card Output is "Address 2" (for the card below that shows Address)

Both elements are from "Sheet 1".

Can you write the DAX statment based on this information, as I can't seem to get it to work properly.
 

Couple of questions:

1.  Are we creating a Measure or a Column?

2.  Do I need to create a Measure/Column for each of the cards?  (See image below)

3.  Once created, how do I apply to the cards?

[vcastello]

Hi,

I think something is missing. As I have understood ...

1.- You have a table named "Lat&Long"
2.- You have a table named "Sheet 1"
3.- In the table named "Sheet 1" you have a column named "Market"
4.- In the table named "Sheet 1" you have a column named "Address 2"
5.- In the table named "Sheet 1" you have other columns (phone, corporate contacts, etc).

Now ... 

A.- Do you have the measure that shows the value of Address 2?
   1.- If you don't, you obviously need it for showing the address. I don't know exactly how your data model is, but it should be                something like ...
                 
                          Address = VALUES(Sheet1[Address 2])
   

   2.- If you do ... you have to rewrite it including the IF(HASONEVALUE( ... wrapper. Something like ....

                            IF(HASONEVALUE('Sheet 1'[Market]), [Address], BLANK() )
       
           Where [Address] is the measure you already have.
 
What this wrapper does is force the measure to be shown ONLY if there is a value in market (the slicer). 

B.- Obviously you will need to place a slicer in the canvas in order to slice the cards. This slicer should be filled with the 'Sheet 1' [Market] Column.

B.- Your questions ....

   1.- We are creating a measure that has to show the values of the "Address2"  column (and all the other columns you want in the canvas).
2.- Yes. You have to create a measure for each of the columns you want to show.
3.- Create the visual through the panel, selecting the one that corresponds to the card. Then you should drop the measure you have created into the Fields box.

[irnm8dn]

@vcastello

Please see my answers inline and the process I completed.

1.- You have a table named "Lat&Long" YES
2.- You have a table named "Sheet 1" YES
3.- In the table named "Sheet 1" you have a column named "Market" YES
4.- In the table named "Sheet 1" you have a column named "Address 2" YES
5.- In the table named "Sheet 1" you have other columns (phone, corporate contacts, etc).  YES

Now ... 

A.- Do you have the measure that shows the value of Address 2?  This is not currently a measure.  Just text found in a column.
   1.- If you don't, you obviously need it for showing the address. I don't know exactly how your data model is, but it should be                something like ...
                 
                          Address = VALUES(Sheet1[Address 2])  This worked.  Now I have a measure for Address2.
   

   2.- If you do ... you have to rewrite it including the IF(HASONEVALUE( ... wrapper. Something like ....

                            IF(HASONEVALUE('Sheet 1'[Market]), [Address], BLANK() )
       
           Where [Address] is the measure you already have.
 
What this wrapper does is force the measure to be shown ONLY if there is a value in market (the slicer). 

*************************************************************
So, here's what I did.

1. I created a measure - Address Measure = Values (Sheet1[Address 2])
2. I created a second measure - Address Visual = if(HASONEVALUE(Sheet1[Market]),[Address Measure],BLANK())
3. I moved Address Visual to the Field, it seemed to work.

Based on your instructions, it looks like I need to first change all the fields I intend to diplay to a Value (i.e. Step 1) because none of them are Values.  

Then I create a second measure and apply the HASONEVALUE statement to have the desired behavior once in the Fields area .  

Do you agree?

[vcastello]

Hi,

Yes, you are correct ...

Well ... More or less ...

You don't have to change your columns,.you have to create measures. One for each column you want to display.

What you need is to display the value of a column. The measure (in this particular case) has to take the values of the column and because it is what you require. If your needs were others you should use another statement but, for this requirement, I think you are on the right path.

Keep on 

Vicente

[irnm8dn]

@vcastello

Thanks again.  I feel like everything I do in PBI is More/Less...  🙂

One last question, am I correct that I have to "transform" the value into a measure using the =Values statement, and then create another measure by using the =if(hasonefilter statement?

The only reason I ask is that I have the original data, the tranformation to the measure and lastly the condition for the filter.  3 in all?

Thanks! 

[vcastello]

Hi again @irnm8dn

Yes ... Lots of things in Power BI and DAX are more/less and yes/no ... Depends on the view.

And again ... more or less.  :- )

What you really need is a wrapper/protector (IF(HASONEVALUE( .... )) that protects a measure that reflects a value of a text from a dataset from showing if something is not selected.

The way I've told you to do it is one way of doing it. From my point of view it's more readable and easier (specially when you are a newbye). If you make a mistake or there is an error ... it will be easier to debug (IMHO).

But the code can be written only once .... like ....

AddressVisual = IF(HASONEVALUE(VALUES('Sheet 1'[Address]), VALUES('Sheet 1'[Address]), BLANK() )

or ....

AddressVisual = 

VAR Address = VALUES('Sheet 1' [Address])
RETURN

IF(HASONEVALUE(AddressVisual, AddressVisual, BLANK() )

Depending on your background and experience .... you end up using one or another way of doing things.

Vicente

[irnm8dn]

@vcastello 

And, another episide of "no good deed goes unpunished"

This is the result, when I follow your syntax:

As an FYI I believe the HASONEVALUE should reference Markert so I am wondering if this is a typo on your feedback. Nonetheless, I can't get the statement to work.

Do I need to make each element a Value, before the HASONEFILTER statment?   

[vcastello]

Yes, there was an error ...

It should be ...

AddressVisual = IF(HASONEVALUE(VALUES('Sheet 1'[Market])), VALUES('Sheet 1'[Address]), BLANK() )

Note that I had another mistake. There is a second ')' after[Market]

[irnm8dn]

@vcastello

Thoughts?

Thanks for sticking with this.

[vcastello · Accepted solution]

Sorry again ... another mistake ... Forget my previous message.

Address1 = IF(HASONEVALUE(Sheet1[Market]),  VALUES(Sheet1[Address 2]), BLANK() )

Vicente

[irnm8dn]

@vcastello

Thanks!  Appreciate the help!