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
Ynew
Helper I
Helper I

A slicer for a pivot table

Hello All,

I need help with coming up with a solution for my problem as below.

To make my question easy to understand I define a simple version of my real data. I have three columns as below:

Customer name               Products        Order Volume

A                                      DDD               3

B                                      EEE                 5

A                                      FFF                 12

B                                      EEE                 10

B                                      HHH                8

C                                      HHH                6

C                                      HHH                4

A                                      HHH                1

 

In fact, I have multiple customers and multiple products with their order volumes. I need to create a slicer. The slicer with have two options to show if a customer is only one type of product or multiple products. So the slicer needs to have options like "Single type" and "Multiple type".

As you see in the above example only customer "C" has "Single type". So I want to have a slicer so that when I have a column visual which shows the total order volume for customers, I can filter the customer to only see the customers with "Single Order" or "Multiple types".

 

I appreciate your kind suggestions and thank you very much in advance.

 

2 ACCEPTED SOLUTIONS

Hi @Ynew

 

It seems your version of Excel does not support DAX variables

 

Please try this instead

 

=
IF (
    CALCULATE (
        DISTINCTCOUNT ( TableName[Products] ),
        ALLEXCEPT ( TableName, TableName[Customer name] )
    )
        > 1,
    "Multiple Type",
    "Single Type"
)

Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
Zubair_Muhammad
Community Champion
Community Champion

Hi @Ynew

 

 

How about Adding a calculated column " Customer Type" then using this as a slicer

 

Customer Type =
VAR DistinctCountofProducts =
    CALCULATE (
        DISTINCTCOUNT ( TableName[Products] ),
        ALLEXCEPT ( TableName, TableName[Customer name] )
    )
RETURN
    IF ( DistinctCountofProducts > 1, "Multiple Type", "Single Type" )

 


Regards
Zubair

Please try my custom visuals

Hello Muhammad,

 

Thank you for your reply. I tried to formulate it in my power BI, but could not figure out your logic. The first and last part of your formulation are not clear for me.

 

Customer Type =
VAR DistinctCountofProducts =
    CALCULATE (
        DISTINCTCOUNT ( TableName[Products] ),
        ALLEXCEPT ( TableName, TableName[Customer name] )
    )
RETURN
    IF ( DistinctCountofProducts > 1, "Multiple Type", "Single Type" )

 

Could you please explain in more details? Have you used this formulation and get the same resutls as we are talking about?

 

Hi @Ynew

The results are shown in the image above.

 

Did you try it as a calculated column with your full data?

 

The Formula just counts the distinct number of products for each customer and if it it is more than 1, it is marked as "Multiple Type"

 

 


Regards
Zubair

Please try my custom visuals

Hi Muhammad,

 

Yes I tried, but the tool does not understand the first and last part of your formulation. The highlited parts I mean.

 

I checked your image, I assume you have formulated in Excel. Even in Excel I could not get the same formulation. Could you please send me the exact formulation you have in power BI?

Hi @Ynew

 

It seems your version of Excel does not support DAX variables

 

Please try this instead

 

=
IF (
    CALCULATE (
        DISTINCTCOUNT ( TableName[Products] ),
        ALLEXCEPT ( TableName, TableName[Customer name] )
    )
        > 1,
    "Multiple Type",
    "Single Type"
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

Thank you very much. I will surely check them since I need to be expert in power BI. I will use it alot for analyzing my data.

Hi @Zubair_Muhammad

 

 

Thank you very very much for your help. It is working now. And sorry, I did not know that there is a link between Excel and Power BI. Good to know that too, I will dig into that to learn how I can use.

 

Many thanks again.

@Ynew

 

1011.png


Regards
Zubair

Please try my custom visuals

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.