Filter Data with Mandatory values

[sjehanzeb]

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

[v-alq-msft · Accepted solution]

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.

[DataInsights]

@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.