Overriding Filters and slicers for a particular measure

[13havya]

Hi All,

I've been struggling to create a measure based on certain conditions as described by stakeholders.

I have two tables being used in this instance - 

Table1(Fact Table)

casekey	File type	File subtype	Other fields....
1	Sales		***
2	Operations		***
3	General	Sales	***
4	Sales		***
5	General	Operations	***
6	General	Sales	***
7	General		***
8	Operations		***
9	General		***

Dim_File_Type(DIm Table)

File_Type

Sales

General

Operations

I want to create a measure that returns a count of all files - with the caveat that files where the subtype is present count towards both the main file type and its respective subtype. 

For instance, files # 3 and 6 count towards both General and Sales, and # 5 counts towards General and Operations. 
So if I were to create a visual, the results should be like so - 

File_Type	# Files
Sales	4
General	5
Operations	3

I understand that certain files will be counted twice - which is how we want the metric to behave. 

Any insights on this would be greatly appreciated. 

Thanks

[vicky_ · Accepted solution]

Try a measure like the following:

# Files = COUNTROWS(FILTER('Table', 'Table'[File subtype] = SELECTEDVALUE(Dim_File_Type[File_Type]) || 'Table'[File type] = SELECTEDVALUE(Dim_File_Type[File_Type])))

[v-weiyan1-msft · Accepted solution]

Hi @13havya ,

@vicky_ nice method! Thank you, for your quick response and the solution provided.
And you can also try code as below.

# Files = 
VAR File_Type = SELECTEDVALUE ( Dim_File_Type[File_Type] )
RETURN
    SUMX (
        'Table1',
        IF (
            'Table1'[File subtype] = File_Type
                || 'Table1'[File type] = File_Type,
            1,
            0
        )
    )

Result is as below.

Best Regards,
Yulia Yan

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

[vicky_ · Accepted solution]

Try a measure like the following:

# Files = COUNTROWS(FILTER('Table', 'Table'[File subtype] = SELECTEDVALUE(Dim_File_Type[File_Type]) || 'Table'[File type] = SELECTEDVALUE(Dim_File_Type[File_Type])))