Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
derekli17001
Frequent Visitor

How to make a table count values until slicer is selected

This is my excel sheet

derekli17001_0-1712791395720.png

On my dashboard- i have 3 customers and the same items with varying conditions of either yes or no. I want it so that when no slicers for specific customers (column c) are selected - i want my table to show like below as a count of all customers":

derekli17001_1-1712792155804.png

However, when a customer slicer is selected - it will show whether the selected customer has the item via yes or no as shown in my excel sheet. Thanks. How do i do this?

1 ACCEPTED SOLUTION

Hi, @derekli17001 

Try the following DAX:

Measure = 
IF(ISFILTERED('Table'[Customer]),
SELECTEDVALUE('Table'[INCLUDE]),
COUNT('Table'[INCLUDE])
)

In the case that no customer is selected

vyohuamsft_0-1712813303884.png

While choosing a customer to slice

vyohuamsft_1-1712813491895.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

5 REPLIES 5
v-yohua-msft
Community Support
Community Support

Hi, @derekli17001 

You may want to consider using Bookmark, as follows:

Create a bookmark and bind it to a Button. Create two tables on the original page, one with count and one with Yes or No. Use Bookmark to record the status of All and hide another table.

vyohuamsft_2-1712807225701.png

 

 

To return to the status of selecting a customer, click another Button.

vyohuamsft_3-1712807300778.png

 

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

 

Is there a fomula/conditional column option rather than using bookmarks?

Hi, @derekli17001 

Try the following DAX:

Measure = 
IF(ISFILTERED('Table'[Customer]),
SELECTEDVALUE('Table'[INCLUDE]),
COUNT('Table'[INCLUDE])
)

In the case that no customer is selected

vyohuamsft_0-1712813303884.png

While choosing a customer to slice

vyohuamsft_1-1712813491895.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

ToddChitt
Super User
Super User

Not sure I understand what you want. Can you give us a sample of what your expected results would look like?




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





This is an example of what my current dashboard looks like when no slicer is selected:

derekli17001_0-1712801585089.png


When i select a customer on a slicer, i will get a respective yes or no for each item as seen below:

derekli17001_2-1712801670896.png

I want it so that when there is no slicer selected - i will get a number of each item for every customer (as shown below) and will only get yes/no when a slicer is selected

derekli17001_3-1712801810320.png

I have attached the dataset i used below:

ITEMINCLUDECustomer
Appointment as Caltex DistributorYesKel Campbell
Distributor Licensed Area - Selective ZoneYesKel Campbell
Sale or assignmentYesKel Campbell
Caltex BrandYesKel Campbell
PremisesYesKel Campbell
Equipment providedYesKel Campbell
IT SystemNoKel Campbell
OPT software and marksYesKel Campbell
Caltex CardYesKel Campbell
Distributor CardYesKel Campbell
TrainingYesKel Campbell
Contribution to advertising/promotionsYesKel Campbell
Investment allowanceYesKel Campbell
Service Fee WorkYesKel Campbell
Credit/deferred payment termsYesKel Campbell
Commission agency for Depot FrontNoKel Campbell
Appointment as Caltex DistributorYesBonney
Distributor Licensed Area - Selective ZoneYesBonney
Sale or assignmentYesBonney
Caltex BrandYesBonney
PremisesYesBonney
Equipment providedYesBonney
IT SystemNoBonney
OPT software and marksYesBonney
Caltex CardYesBonney
Distributor CardYesBonney
TrainingYesBonney
Contribution to advertising/promotionsYesBonney
Investment allowanceYesBonney
Service Fee WorkYesBonney
Credit/deferred payment termsYesBonney
Commission agency for Depot FrontYesBonney
Appointment as Caltex DistributorYesGeraldton
Distributor Licensed Area - Selective ZoneYesGeraldton
Sale or assignmentYesGeraldton
Caltex BrandYesGeraldton
PremisesYesGeraldton
Equipment providedYesGeraldton
IT SystemNoGeraldton
OPT software and marksYesGeraldton
Caltex CardYesGeraldton
Distributor CardYesGeraldton
TrainingYesGeraldton
Contribution to advertising/promotionsYesGeraldton
Investment allowanceYesGeraldton
Service Fee WorkYesGeraldton
Credit/deferred payment termsYesGeraldton
Commission agency for Depot FrontYesGeraldton

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.