DAX, Slicer Syncing and Industry Selection

[dgwilson]

This I thought would be simple. I've spent some time pooring over the web and books for a solution... and haven't got there.

I've built an example pbix to demonstrate what needs to be solved. It's available here: Industry Selection 

This is a cut down example from a much larger model that has 11+ million rows of data behind it.

1. In short a user uses the SELECT page to choose the company they want to examine.

The "Company name" slicer is sync'd across subsequent pages/tabs.

2. The user can use the transaction detail page to look at the financial details of the selected company.

The real version of this has drill down and graphs etc to show fiancial performance for the given financial year.

3. Tab three is for an Industry comparrison. The selected compant (from the SELECT tab) is from the Mining or Education or Media industry. This page is intended to show the financial performance for the selected company and others from the same industry.

TAB 3 is where the issue is. I get tied up in knots with Context/filter evaluation because of the synd'd slicer. I need the Industry from the company to show the other similar industries.... but keep getting stuck on the one selected company.

Really super keen to see if this can be solved and what approaches are taken.

[kentyler]

I think you could do this if you expand your data model.

Part of the requirement is that the record for each company needs to "know" the other companies that are similar to it

If you add this table

Company Name	Industry	similar company
Company A	Media	Company F
Company A	Media	Company I
Company f	Media	Company a
Company f	Media	Company I
Company i	Media	Company F
Company i	Media	Company a

Then you could create a relationship between the company record (which is being filtered by the slicer) and the company column in this table... which would return all the matching companies.
You might have to write some calculated columns or measures to retrieve the information about the related company you wanted to display

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

[dgwilson]

Thank you @kentyler  and @JarroVGIT for your replies. I'll take your suggestions on board and experiment further. So pleased you were able to understand what I'm trying to do here.

- David

[dgwilson]

I’ve come up with the below… it feels a bit like a dirty hack.

Step 1 – duplicate my Company table – it is not linked to any of the other tables.

Company_unFiltered = CALCULATETABLE(Company)

Step 2 – create measure for field values in Table

_Amount_4_Industry =

VAR industry_local = SELECTEDVALUE(Company[Industry])

VAR myGLCode = MIN(Transactions[GL Code])

VAR myCompany = SELECTEDVALUE(Company_unFiltered[Company Name])

VAR amount = CALCULATE(

                    SUM(Transactions[Amount]),

                    ALL(Transactions)

                    , Company_unFiltered[Industry] = industry_local

                    , Transactions[Company] = myCompany

                    , Transactions[GL Code] = myGLCode

                    )

return

        IF(industry_local IN FILTERS(Company_unFiltered[Industry]),

            amount

            )

Step 3 – The matrix columns are from the Company_unfiltered table.

… have just noticed that my column totals are wrong. Hmmmmph! Dam.

[JarroVGIT]

That was indeed one of the solutions I was trying out as well but I figured you didn't want to create very complicated measures all over your report 😛 

Anyway, you totals can be fixed! Somewhere you need an IF statement in your measure that determines if there is a filter on the GL Code column (as you are using that as rows) and if that is the case then apply the current measure. If that is not the case then caluclate something else. Totals rows are evaluated just as normal rows but with fewer filters (e.g. not on the GL code). But in your measure you use VAR myGLCode = MIN(Transactions[GL Code]) what also is evaluated when calculating the total. You can put in the above mentioned IF statement probably in the VAR amount statement, something like 

VAR amount = IF(ISFILTERED(Transactions[GL Code], 
          CALCULATE(
                    SUM(Transactions[Amount]),
                    ALL(Transactions)
                    , Company_unFiltered[Industry] = industry_local
                    , Transactions[Company] = myCompany
                    , Transactions[GL Code] = myGLCode
                    ),
         CALCULATE(
                    SUM(Transactions[Amount]),
                    ALL(Transactions)
                    , Company_unFiltered[Industry] = industry_local
                    , Transactions[Company] = myCompany
                    ))

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[dgwilson]

And now both Calculates etc can be replaced by a SUMX

_Amount_4_Industry =

VAR industry_local = SELECTEDVALUE(Company[Industry])

VAR amount2 = SUMX(Transactions, [Amount])

return

IF(industry_local IN FILTERS(Company_unFiltered[Industry]),

amount2

)

Solution inspiration goes to Phil Seamark. I discussed this with him last night and this morning.

I have additionaly tried to "Edit Interactions" and disable the link between the hidden slicer and the Matrix... but that leaves the DAX Measure not being able to know what the selected company is.... as far as I can tell.

- David

[JarroVGIT]

Hi @dgwilson ,

Well, I played around a lot (😅) but couldn't find an exact match for what you are trying to achieve. I thought I had a solution but that didn't work out in the end. The current road I am leaning towards is to have two slicers on your SELECT page. The first slicer is based on Industry table, the second slicer is the Company table. Maybe you can create a new column in the Company table that concatenates the name and Industry like this "Company E (Education)" so your users can see the industry is attached to their company as a helper. Then don't sync the company slicer to the last page, but do sync the industry slicer to the last page. That will fit your requirement but your users would have to click two slicers rather then one. A bit of explanation on the SELECT page might go a long way in user adoption..

Hope this helps, I thought this would be a lot easier to achieve but eventually I think this is the best way forward without overcomplicating the whole report.

Also: big KUDO's for adding an example PBIX!👍

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂