Forum Discussion

lcasey's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

Filter report based on Slicer?

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!

 

 

 

  

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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

     

22 Replies

  • Dog's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    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's avatar
      lcasey
      Icon for Post Prodigy rankPost Prodigy
      9 years ago

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

       

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        9 years ago

        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