Measure Interaction not working

[shrock777]

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.

https://community.powerbi.com/t5/Desktop/Count-and-Sum-of-Balanced-Orders-and-Tasks/m-p/1082554#M501...

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))

[v-alq-msft]

Hi, @shrock777 

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.

[shrock777]

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

[Ashish_Mathur]

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.

[shrock777]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Ensure your formulas are already wriiten there.  Clearly show your problem there.

[shrock777]

Hello @Ashish_Mathur ,

Below is the link

https://filebin.net/7du6mez10dpfkdlw

Thanks

[Ashish_Mathur]

Hi,

That link contains a zipped folder with no PBI file in that folder.

[VijayP]

@shrock777 

In the Countrow function, before the last ")", use VALUES(Context of filter) then it should work

[shrock777]

@VijayP 

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))