Forum Discussion
Advocate IIIgnore selected columns and filter while calculating measures
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!!
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] ) )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
V-lianl-msftCommunity Support
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.
DeemoThanks 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
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] ) )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.