Forum Discussion

sjehanzeb's avatar
sjehanzeb
Resolver I
6 years ago
Solved

Filter Data with Mandatory values

I have an accounting data with many fields. Is there a possibility to filter data where 

 

A journal ID has specific two data values (mandatory). i.e. show only those entries which has specific account selected. if one of the account is not there it should not be part of the list. 

 

eg. From the below sample list only I want Journal IDs where cash and payable are both there. from below list only 001 should be filtered out. How can i do that.?  

 

data sample

J ID       Account   Debit  Credit

001       Cash         10

001       Payable              10

002       Cash         100

002       Capital                100

003       Payable     10

003       Bank                    10

Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, sjehanzeb 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.


    Table:

     

    You may create a measure as below.

    Visual Control = 
var _jid = SELECTEDVALUE('Table'[J ID])
return
IF(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[J ID]=_jid&&
            'Table'[Account]="Cash"
        )
    )>0&&
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[J ID]=_jid&&
            'Table'[Account]="Payable"
        )
    )>0,
    1,0
)

     

    Finally you need to put the measure in the visual level filter to display the result.

     

    Best Regards

    Allan

     

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

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    6 years ago

    sjehanzeb try this:

     

    1. Create a calculated table

    JournalAccount1 = DISTINCT ( Journal[Account] )

    2. Create a calculated table

    JournalAccount2 = DISTINCT ( Journal[Account] )

    3. Create a slicer for calculated table #1

    4. Create a slicer for calculated table #2

    5. Create a measure

    Has Both Accounts =
VAR SelAcct1 =
    SELECTEDVALUE ( JournalAccount1[Account] )
VAR SelAcct2 =
    SELECTEDVALUE ( JournalAccount2[Account] )
VAR SelJrnl =
    SELECTEDVALUE ( Journal[Jrnl ID] )
VAR JrnlTable1 =
    FILTER (
        ALL ( Journal ),
        Journal[Jrnl ID] = SelJrnl
            && Journal[Account] = SelAcct1
    )
VAR JrnlTable2 =
    FILTER (
        ALL ( Journal ),
        Journal[Jrnl ID] = SelJrnl
            && Journal[Account] = SelAcct2
    )
VAR RowCount1 =
    COUNTROWS ( JrnlTable1 )
VAR RowCount2 =
    COUNTROWS ( JrnlTable2 )
VAR Result =
    IF ( RowCount1 >= 1 && RowCount2 >= 1, 1, 0 )
RETURN
    Result

    6. Add the measure [Has Both Accounts] to the visual filter, where [Has Both Accounts] = 1

     

    Note: don't create any relationships with the calculated tables.

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, sjehanzeb 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.


    Table:

     

    You may create a measure as below.

    Visual Control = 
var _jid = SELECTEDVALUE('Table'[J ID])
return
IF(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[J ID]=_jid&&
            'Table'[Account]="Cash"
        )
    )>0&&
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[J ID]=_jid&&
            'Table'[Account]="Payable"
        )
    )>0,
    1,0
)

     

    Finally you need to put the measure in the visual level filter to display the result.

     

    Best Regards

    Allan

     

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