Urgent Help require please - 1 slicer to filter 2 columns.

[GKK1]

Hi Power Bi Community,

Hi @v-junyant-msft , 

I would really appreciate your help. 
I have two sicers, one is Opertational deparment and the other is Departments. And If I select Gift Card from my Department slicer then it will give me the worked hours against Gift Card and all the Sales in different Sales departments against the Gift Card as well. 

Similar results for the Credit Card department.

Thanks in advance for your support.

My Data model:

The Data looks like this:

My operational slicer is working fine, if I select any of it then it gives me Sales and Work force hours as expected:

Now I want to use my Department Slicer to work in a way that it will filter two columns, the Departments and Operational Departments both at the same time if I select one. Currently, department slicer is not working as I want it to work. If I am selecting Credit Card, then it is showing me only the data in Work hours and no sales against the Credit Card in any department. As shown in the screen shot.

I want to see the results as follows with the Department Slicer.

Department Slicer

Gift Cards

If I select Gift Card from Slicer then I should see these results. To me it seems like I have one department slicer which will filter one column from work force hours table and two columns from Sales , i.e. Departments and Operational Departments both.
Results :

Work force Dept	Op    Dept	Work Hours
Gift Cards	Gift Cards	45

Sales Dept	Op Dept	Sales Dollars
Kid's clothing	Gift Cards	6000
Men's Cloting	Gift Cards	6000
Wommen's Clothing	Gift Cards	4500
Toys	Gift Cards	4000
Cosmetics	Gift Cards	5000
Electronics	Gift Cards	3500
Home Décor	Gift Cards	4500

[Anonymous · Accepted solution]

Hi @GKK1 ,

Replace "Values" in the matrix with the following two measures:

Sum_of_Sales_Dollars = CALCULATE(
    SUM('Sales Hours'[Sales Dollars]),
    FILTER(
        'Sales Hours',
        'Sales Hours'[Measure] = 1
))

Sum_of_Work_Hours = 
CALCULATE(
    SUM('Work Force Hours'[Work Hours]),
    FILTER(
        'Work Force Hours',
        'Day'[Measure2] = 1
    )
)

The results are as follows:

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

[Anonymous]

Hi @GKK1 ,

Please try this way:
First of all, please set the relationship like this:

Then create a new measure by using the DAX below:

Measure = 
VAR Selected = SELECTEDVALUE(Sheet3[Hours Depoartment Slicer])
RETURN
IF(
    Selected = "Credit Card" || Selected = "Gift Cards",
    IF(
        SELECTEDVALUE('Sales of the store'[Op Dept]) = Selected, 1, 0),
        IF(
            SELECTEDVALUE('Sales of the store'[Sales Dept]) = Selected, 1, 0)
)

Click on the visual object and put the measure into "Filters on this visual":

The results are as follows:

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

[GKK1]

thanks but it is not working completely. It broke the Operational departments a bit. I can't see Cash in the sales for Op_department slicer:

And untill I do not select any department I cant see anything from the sales data. I want if I do not have anything selected from the slicer, all the data is shown. 

With no selected the results should be everything like this. 

[Anonymous]

Hi @GKK1 ,

Oh sorry I forgot another slicer, please change the DAX into this:

Measure = 
VAR Selected = SELECTEDVALUE(Sheet3[Hours Depoartment Slicer])
RETURN
IF(
    ISBLANK(Selected),
    1,
    IF(
    Selected = "Credit Card" || Selected = "Gift Cards",
    IF(
        SELECTEDVALUE('Sales of the store'[Op Dept]) = Selected, 1, 0),
        IF(
            SELECTEDVALUE('Sales of the store'[Sales Dept]) = Selected, 1, 0)

)
)

Nothing else needs to change.

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

[GKK1]

Thank you very much, it is working with this DAX measure. Just one last thing please.  As shown in the screenshot, if I select a Department for example Cosmetics and then select Credit Card as well, then it is showing me all the Credit Cards and not the Credit Card just from the Cosmetics. Which should be kind of the behaviour of the slicer if we select multiple slicers at the same time.

[Anonymous]

Hi @GKK1 ,

Start by adjusting the relationship as shown below:

With the above DAX and all operations preserved, add a measure2 to the Employee hours table:

Measure2 = 
VAR Selected = SELECTEDVALUE(Sheet3[Hours Depoartment Slicer])
RETURN
IF(
    ISBLANK(Selected),
    1,
    IF(
       SELECTEDVALUE('Employee hours'[Work force Dept]) = Selected,
       1,
       0
)

The results are as follows:

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

[GKK1]

Thank you very much, I really really appreciate. And sorry to bother you again. 
My client asked me show this data in a matrix as well on the next tab in the report on daily basis. 
This is working with the Op departments 100% because we do have relationship there. But not with the departments. I am sharing the screenshot and the file with added Day table.

https://drive.google.com/file/d/1mguXi5Xw2r_4rrhkCl_W8x9yp-Udm9Zc/view?usp=drive_link 

[Anonymous · Accepted solution]

Hi @GKK1 ,

Replace "Values" in the matrix with the following two measures:

Sum_of_Sales_Dollars = CALCULATE(
    SUM('Sales Hours'[Sales Dollars]),
    FILTER(
        'Sales Hours',
        'Sales Hours'[Measure] = 1
))

Sum_of_Work_Hours = 
CALCULATE(
    SUM('Work Force Hours'[Work Hours]),
    FILTER(
        'Work Force Hours',
        'Day'[Measure2] = 1
    )
)

The results are as follows:

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

[GKK1]

Hi @Anonymous ,

I have another really urgent request. Now my client wants me to optimize the slicers into one. So I need to have just the operational departments as the slicers and in the Cash Departments they want to see all the other common departments. The function will remain kind of the same except the Cash operations department will have drop down menu with those cash departments and show only the results with those. We have the PBI file attahced in the chat history and the required results would be:

Slicer	Result values	Drop Down Slicer
Credit Card	Credit Card
Gift Card	Gift Card
Support	Support
Cash	Cash	Cosmetics
		Electronics
		Home Decore
		Kid's Clothing
		Men's Clothing
		women's Clothing
		Toys

I would really really appreciate a quick help here.

Best reagrds,

GK

[Anonymous]

Hi @GKK1 ,

If you want all but the cash department to just see this part of the slicer and the corresponding content:

And only the cash department can see this part of the drop-down menu and the corresponding content:

I'm sorry, but this is not available in Power BI at this time. I could probably implement this slicer using the method of setting up the RLS, but then the information captured by all but the cash department would be crippled (even what they should be able to see may not be visible).

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

[GKK1]

@Anonymous  Is it possible to have the results like this:

Slicer	Result values	Drop Down Slicer
Credit Card	Credit Card	Credit Card
Gift Card	Gift Card	Gift Card
Support	Support	Support
Cash	Cash	Cosmetics
		Electronics
		Home Decore
		Kid's Clothing
		Men's Clothing
		women's Clothing
		Toys

And the result values for work force hours and Sales dollars will be the same as we had earlier that if I select Credit Card then it will have the hours against the credit card and Sales dollars against the Credit Card, same with Gift Card.
But when I select Cash, then the slicer will show me all the other options and if I select CASH from slicer then it will show me all the values, however, if I want I would be able to select just one Department under Cash and would be able to see that department's Sales dollars and work Hours.

Best regards,

GK

[Anonymous]

Hi @GKK1 ,

I'm sorry to say that this really isn't possible. I've tried, and in any case to achieve the result you need you need at least two slicers

Best Regards,
Dino Tao

[GKK1]

@Anonymous , thanks for checking.
Would it be possible to reduce redundancy, that I will use only one Departments Slicer. And the results would be that if I select Electronics then I will get same results from Work hours Data but in the Sales I will get the results only from Cash Sales and it will filter out Sales from the Credit Card and Gift Cards from the Table and the matrix both? The Total should be $4500 only which is from Cash. We have Credit Cards and Gift Cards covered separately in the Departments for Sales , and that should remain the same.

[Anonymous · Accepted solution]

Hi @GKK1 ,

Please check whether this is what you want?

Best Regards,
Dino Tao

[GKK1]

@Anonymous , yes it is. Thank you very much once again. Really appreciate once again. I have seen that you have added in the Measure to get the results.

Measure =

VAR Selected = SELECTEDVALUE(Departments[Departments])

RETURN

IF(

    ISBLANK(Selected),

    1,

   IF( Selected = "Credit Card" || Selected = "Gift Cards" ,

    IF(

        SELECTEDVALUE('Sales Hours'[Op Dept]) = Selected, 1, 0),

        IF(

            SELECTEDVALUE('Sales Hours'[Sales Dept]) = Selected && SELECTEDVALUE('Sales Hours'[Op Dept]) = "Cash", 1, 0)

[GKK1]

Thank you very much @Anonymous . I really appreciate all of your support. I would really love to learn Power BI DAX from you more in the future. 🙂

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[GKK1]

Thank you very much @lbendlin for your guidance.

Here is the link to the sameple file. https://drive.google.com/file/d/1mguXi5Xw2r_4rrhkCl_W8x9yp-Udm9Zc/view?usp=drive_link 

The results I want that if I will select Credit Card from the department, then my tables will show me the all the departments where payment mode in Sales table is Credit Card and the worked hours from Credit Card department. As underlined in the screen below.

I have Sales Departments and Work Hours Departments mapped so that if I will select any department it will show me the hours and sales both from the same department. 
The issue is where I have the same Operational departments name in the Work Hours department. i.e. Credit Card and Gift Card.

Please let me know if it clarifies my question. I would really appreciate your support to resolve this issue. Thanks

[lbendlin]

please check the link - access is denied.