Forum Discussion
Measure Interaction not working
Hello All,
Below is the link to the dataset and I tried below DAX to calculate Count of Orders in Balance. The numbers seems are fine but visuals are not interacting.
Any help is appreciated @TomMartens , @Zubair_Muhammad @jdbuchanan71 @Greg_Deckler @Sean @MarcelB,@parry2k, @amitchandak,@Ashish_Mathur
Count and Sum of Balanced Orders and Tasks
Count of Orders in Balance =
VAR __Table =
GROUPBY(
'Table',
[Order No],
"Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity])
)
RETURN
COUNTROWS(FILTER(__Table,[Balance]=0))9 Replies
Anonymous
In the Countrow function, before the last ")", use VALUES(Context of filter) then it should work
Thanks for the reply. I tried below and got this error.
The VALUES function expects a column reference expression or a table reference expression for argument '1'
Count of Orders in Balance = VAR __Table = GROUPBY( 'Table', [Order No], "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity]) ) RETURN COUNTROWS(FILTER(VALUES(__Table),[Balance]=0))
Hi,
I'd like ot help but the data you have shared in the other thread is no clear. Please share the input tables, describe the business question and show the expected result.
Hello Ashish_Mathur
Below is the detail level
This might be a very simple issue but it turns out complex for me. Below is the table. I am trying to calculate tasks and orders which are balanced(which means sum Order Count = 0).
Count of distinct Orders = Calculate(distinctcount(order no))
Count of Orders in Balance =
Calculate(distinctcount(order no)),Filter(SUM(Order/Task Quantity = 0))
Count of Orders not in Balance =
Calculate(distinctcount(order no)),Filter(SUM(Order/Task Quantity <> 0))
Count of distinct Tasks = Calculate(distinctcount(task order no))
Count of Tasks in Balance =
Calculate(distinctcount(task order no)),Filter(SUM(Order/Task Quantity = 0))
Count of Tasks not in Balance =
Calculate(distinctcount(task order no)),Filter(SUM(Order/Task Quantity <> 0))
Expected Result (KPI's)
- Count of distinct Orders = 1
- Count of Orders in Balance = 0
- Count of Orders not in Balance = 1
- Count of distinct Tasks = 7
- Count of Tasks in Balance = 5
- Count of Tasks not in Balance = 2
I have Order No as a slicer, For example, if I select Order No 1350000-11 then it should display Count of Orders in Balance = 1 and Count of Orders not in Balance = 0. Same applies for the date range, If I select
Counts of Balanced Tasks is visualized in clustered bar chart and interactions should work as expected
Please help me with the above issue. Thanks!!
Order No Task Order No Task Completed Date Order/Task Quantity 1350000 8/9/19 0 1350000 1350000-11 7/31/19 0 1350000 1350000-12 8/1/19 0 1350000 1350000-13 8/9/19 0 1350000 1350000-14 8/9/19 0 1350000 1350000-15 8/9/19 0 1350000 1350000-8 8/1/19 -370 1350000 1350000-8 8/1/19 -90 1350000 1350000-9 7/31/19 0 1350000 1350000-9 7/31/19 4 1350000 1350000-9 7/31/19 95 Hi,
Share the link from where i can download your PBI file. Ensure your formulas are already wriiten there. Clearly show your problem there.
Hi, Anonymous
I think the solution suggest by @Greg_Deckler is correct. As is mentioned in the last post, 'When I click on the clustered bar chart(TNB) the TB and Count of Task should be 2 but they are not interacting. ', I assume that you want to filter another measure with a clustered bar chart which is a measure. It is unavailable to interact like above. There is no column on 'Axis' in the bar chart.
I created data to reproduce your scenario. Here are the measures.
Count of Distinct Orders = var __Table = GROUPBY( 'Table', [Order No], "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity]) ) var _re = COUNTROWS(__Table) return IF( ISBLANK(_re), 0, _re ) Count of Orders in Balance = var __Table = GROUPBY( 'Table', [Order No], "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity]) ) var _re = COUNTROWS(FILTER(__Table,[Balance]=0)) return IF( ISBLANK(_re), 0, _re ) Count of Orders not in Balance = var __Table = GROUPBY( 'Table', [Order No], "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity]) ) var _re = COUNTROWS(FILTER(__Table,[Balance]<>0)) return IF( ISBLANK(_re), 0, _re ) Count of Distinct Tasks = var __Table = GROUPBY( 'Table', [Task Order No], "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity]) ) var _re = COUNTROWS( FILTER( __Table, [Task Order No]<>BLANK() ) ) return IF( ISBLANK(_re), 0, _re ) Count of Tasks in Balance = var __Table = GROUPBY( 'Table', [Task Order No], "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity]) ) var _re = COUNTROWS( FILTER( __Table, [Task Order No]<>BLANK()&& [Balance]=0 ) ) return IF( ISBLANK(_re), 0, _re ) Count of Tasks not in Balance = var __Table = GROUPBY( 'Table', [Task Order No], "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity]) ) var _re = COUNTROWS( FILTER( __Table, [Task Order No]<>BLANK()&& [Balance]<>0 ) ) return IF( ISBLANK(_re), 0, _re )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.
Hello v-alq-msft ,
Thank you for your reply. When I click on individual Task Order No the measures are interacting but when I click on clustered bar chart number they are not interacting. Well, the clustered bar chart in the axis shows the aging of the count in the range of 0-15 days, 15-30 days, and 30+ days but this shouldn't change anything with the measure by default.
Thanks
