Custom Aggregation in a Matrix by Using another query?

[alaynanich]

Hi PBI,

So I have a sales matrix that i want to drill down into 2 hierarhcies of data - D1 and its subgroups. The issue comes in getting the distinct counts of customers - we do not have a database that accepts Direct Query, and we have far too much data to import into PBI Pro, so I have to aggregate as much as possible. But my clients want to see these distinct counts at both hierarchies, leading me to create 2 seprate queries to find distinct counts as those levels. The easiest but unlikeable workaround would be to just create seperate pages for the teams to look at each level of data. But i was wondering if it was possible to actually "replace" a subtotal in a matrix with another value from another query? So for example below, the first sum of online customers belongs to D1's distinct count, while the second column is the actual distinct counts per subgroup. Is there a way to replace the 390,870 subtotal (which is just summing the subgroup counts) with the prefereed 276,941 number? Thanks!

[Daniel29195]

hello @alaynanich 

since you are using a matrix, you can take advantage of the isinscope function to control the calculation per each level.

so you can create a measure following the below pattern : 

measure = 

switch(

true() , 

isinscope( tbl_name[col_name]) ,  calculation , 

insinscope(tbl_name[col_name_1]) ,  calculation_1 ,

....

)

https://dax.guide/isinscope/

let me know if that works for you .

If this answers your question  ,  mark it as the solution ✅ so can you can help  other people in the community find it easily .

[alaynanich]

Thank you for the reply @Daniel29195 , I tried the isinscope method and it worked for the high level D1, but not for the subgroups. I attached a sample file sample for any insight - I think it may be because i'm using another "sku"  table to connect the primary keys? Should I rework my model first?
https://drive.google.com/file/d/15pCLA4aNKvYoY0eC1dCN8aQSEBUAh9gn/view?usp=sharing

[Daniel29195 · Accepted solution]

@alaynanich 

output

Measure = SWITCH(
    TRUE(),
     ISINSCOPE(DP_Level[discoveryprodcategory_id]),SUM(DP_Level[online_Customers]),
    ISINSCOPE(DC_Level[discoverydept_num]),SUM(DC_Level[online_Customers])
)
   

just need to reverse the order of the levels . 

when using isinscope, you should always start with the lowest level ,and go  up .

hope this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[alaynanich]

@Daniel29195 Thanks you so much!!! This will be so helpful in the future - excited to learn a new function