Ignore selected columns and filter while calculating measures

[Deemo]

Hi All,

Scenario is that we have to display those flows which are being used in more than one department based on selected date filter and department.

Below is the sample data(department ,flow are dimensions)

Department	Flow	Date	Value
Dept 1	Flow1	May	7
Dept 1	Flow2	June	2
Dept 1	Flow3	July	10
Dept 1	Flow4	Aug	6
Dept 2	Flow3	June	15
Dept 2	Flow5	June	4
Dept 3	Flow1	May	19
Dept 3	Flow3	July	3
Dept 3	Flow4	Aug	4

Axis= Flow

Legend = Department

Value = sum of Value

If we select,Department : All  and Date : June - July

Result should be Flow3 , in legend we will have Dept1(10), Dept2(15) and Dept3(3)

If we select,Department : Dept1 and Date : May

Result should be Flow1 , in legend we will have Dept1(10)

When we simply create a measure with DISTINCTCOUNT(Department), it doesn't work as in the table I have department,flows,values columns. Even after using ALLEXCEPT , it is including department column while calculating count and which always shows 1.

Please help!!

[V-lianl-msft]

Hi @Deemo ,

Sorry, I can't know what the result you want. You can describe it in more detail, or share the sample pbix file.

How is "no. of executions" calculated?
What do you want by calculating "distinctcount (Department)"?

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

[Deemo]

Thanks for the reply Liang.Added sample data. Please check.

I actually want to calculate count (distinct department) group by flow and date(as per the date selected in filter). We need to display only those flow which are being used more than once in different account.

[V-lianl-msft · Accepted solution]

Hi @Deemo ,

Please create a measure and apply it in visual level filter.

Measure 3 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Department] ),
    ALLSELECTED ( 'Table'[Date], 'Table'[Department] ),
    REMOVEFILTERS ( 'Table'[Department] )
)

.PBIX file

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

[Deemo]

Thanks Liang.

I am getting error for REMOVEFILTERS (Cannot find name) and second part of ALLSELECTED (Unexpected parameter)

ALLSELECTED ( 'Table'[Date], 'Table'[Department] ),
    REMOVEFILTERS ( 'Table'[Department] )

I am connected to SSAS.