Filter report based on Slicer?

[lcasey]

Hello

I have a question for you regarding the DAX engine and Power BI.  BSA has Multiple companies that they manage and enter accounting transactions for. Each Company is its own separate database.  Currently, they perform intercompany transactions and Post those transactions to Various P & L accounts.  I have created a structured Hierarchy as best practice in the DAX model that Rolls up all the companies into a neat and consolidate tree.  The issue is that I can find no way to perform a filter based on a slicer selection.

When the slicer has no selection or everything is selected That represents a consolidation at the very highest level.  If this is  true,  then I need to remove (Filter Out)  all Intercompany transactions from the display.  If JUST the Association is selected I then need to Include every transaction including intercompany transactions.  I can determine the slicer selection and Intercompany transactions using the 2 formulas below:

Org Filter =

IF (

    HASONEVALUE ( '00-COASlicer'[Org2] ),

    VALUES (  '00-COASlicer'[Org2]  ),

    "ALL"

)

Intercompany = IF('00-COA'[ACTNUMBR_5] = "9999" || '00-COA'[ACTNUMBR_5] = "9998","Yes","No")

Problem:

I have no way to tell the report to filter out Intercompany  transactions when Org Filter returns “ All”  and Include all Intercompany Transactions when Org Filter returns “ The Association”

Is it even possible in the Power BI analytical platform to create an automatic filter when an end user selects a slicer option?  I could place a simple Check Box in the report that users could select or deselect depending on what they are viewing in the organizational structure,  but BSA would like to automate that so the end user does not have to make a choice.

Please let me know your thoughts as I will need to scrap everything I have done and start over if there are no solutions to allow an automated filter based on the selected Slicer. If this is something you could assist in Programming we could really use your help in coming up with some custom code that would automate this reporting need.

Thanks!

[Anonymous]

This feature is added to the Power BI desktop February 2018 Update. 

Feature List Power BI Desktop Update - February 2018 video

You can download Power BI Desktop from here. 

[Dog]

Hi, 

not entirely sure I follow... how is this currently being displayed as an output. 

if you have a table that is outputting a SumOfTransactions value can you pass the filter at that level. 

so 

MyOutput:=

var ChkFilter = [Org Filter]

return IF(ChkFilter = "All", CALCULATE([SumOfTransactions], [InterCompany] = "No"), CALCULATE([SumOfTransactions]))

not sure if that's of any assistance...

[lcasey]

This is Interesting, but I end up getting the following error:

[Sean]

I think you need to add the FILTER function because [InterCompany] is a Measure

FILTER( ALL(Table), [InterCompany] = "No")

but this is @Dog's formula so lets see what he has to say

[Dog]

wasn't sure if it was a measure or CC 

in theory though I wouldn't have expected that error message on a column, unless the DAX engine is trying to convert it, 

the filter is probably the best option as I think this is not converted by the DAX engine. 

do you have a copy of you measure (the one that's calculating the values)

[Sean]

@lcasey

Common Naming Convetions

• Column reference:  Table[Column Name]
• Measure Reference:  [measure name]

That's why I assumed it was a Measure (I only scanned thru your original post)

Plus that's when I've seen the error you posted...

[Dog]

🙂 yeah still quite new to this - I must remember to be precise with syntax. Sorry

[Dog]

your other option I suppose .....

MyOutput :=
VAR ChkFilter = [Org Filter]
RETURN
IF (
ChkFilter = "All",
CALCULATE (
[SumOfTransactions],
'00-COA'[ACTNUMBR_5] <> "9999"
|| '00-COA'[ACTNUMBR_5] <> "9998"
),
CALCULATE ( [SumOfTransactions] )
)

[lcasey]

Intercompany is a calculated column.   I wonder if I need to make it a measure, I would then need to aggregate all the accounts if using a measure. I am still working on this, and trying all different ways.

In SSRS it is extreemly simple as all I would do is create a parameter and use that as my filter on the dataset. DAX is a bit different, but I think I am on the right track, 

[lcasey]

Here is a link to the issue:  https://www.screencast.com/t/wL07Zwc1njKH

Is it possible to automate what I am trying to do in this video?

[Vvelarde · Accepted solution]

@lcasey

Maybe is something like this:

FILTROINTERCOMPANY =
IF (
    FIRSTNONBLANK ( Table1[Intercompany], Table1[Intercompany] )
        < IF ( ISFILTERED ( Table1[Org1] ), 2; 1 ),
    1,
    BLANK ()
)

In Visual Level Filter Select when FiltroIntercompany is not Blank

[lcasey]

It may be some way the fields are modeled?

Error Message:
MdxScript(Model) (3, 5) Calculation error in measure '00-COA'[FILTROINTERCOMPANY]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

[Vvelarde]

@lcasey

Can you change the Intercompany type to Number?

[lcasey]

I tried that too. It still doesnt work. 

[Vvelarde]

@lcasey

When you select your Intercompany Field in Modeling Data Type, What show?--String or Whole Number.

[lcasey]

It shows as a whole number

[Vvelarde]

@lcasey

Can you show me your measure FILTROINTERCOMPANY.?

Thanks

[lcasey]

I am wondering if a link to the PBIX may help?

[Vvelarde]

@lcasey

Yes, will be great to find a more quicker solution.

[lcasey]

I sent you a message,

I am also experimenting with restructuring my filters.

I have a column for Company, and each and every account is classified to a company. I can easily make that a slicer so the end user can select a company and get all the transactions that they need.

Then, In another column, I can create consolidated column and flag the intercompany transactions so that they can be removed from the selection.

I would need 2 slicers rather than 1 doing it that way.  But a User would simply choose the consolidated filter selection or a Company selection.