Forum Discussion

WBscooby's avatar
WBscooby
Icon for Helper III rankHelper III
5 years ago

Using filters in measures/ Applying to a visual

Hi   I am getting in a mess with filters again and would appreciate some guidance. I have created a simple replica of my file as my data is quite sensitive.   Essentially I have 2 key tables - Sa...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi  WBscooby ,

Here are the steps you can follow：

1. Create measure.

Measure 2 =
var _select=SELECTEDVALUE('SalesTransactions'[Outcome Level])
return
SWITCH(
    TRUE(),
    _select="High",
    CALCULATE(DISTINCTCOUNT('SalesTransactions'[Link ID]),FILTER(ALL('SalesTransactions'),'SalesTransactions'[RepeatHigh]=MAX('SalesTransactions'[RepeatHigh])&&'SalesTransactions'[Cohort]=MAX(SalesTransactions[Cohort])&&'SalesTransactions'[Outcome Level]="High")),
    _select="Low",
    CALCULATE(DISTINCTCOUNT('SalesTransactions'[Link ID]),FILTER(ALL('SalesTransactions'),'SalesTransactions'[RepeatLow]=MAX('SalesTransactions'[RepeatLow])&&'SalesTransactions'[Cohort]=MAX(SalesTransactions[Cohort])&&'SalesTransactions'[Outcome Level]="Low")),
    BLANK()
)

2. Result.

Outcome level = High:

Outcome level = Low:

When you select Low, your Link ID has only eight totals in the table, so it will appear that when you select "Low", the number in the pie is reduced, so you can see if there is an error in which step, which causes the total to decrease.

 

 

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

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

WBscooby's avatar
WBscooby
Icon for Helper III rankHelper III
5 years ago

This is great, thank you. I created Measure 3 which replicated your measure but removed the additional filter on outcome level. This now gives the correct results for Q1 and for all other quarters with the exception of 'Low' on Q2. There are only 11 distinct IDs for the Q2 and this works perfectly on 'High' but when I select 'Low' it counts 11 for both Repeat Customer 0 and Repeat Customer 1. I have played around with it but cannot work out why this is. There should be a distinct count of 11 LinkIDs for Repeat Customer 0. 

 

Do you have any idea why this might be happening? Thank you and sorry for the continued questions! It is really helping my understanding.

 

https://1drv.ms/u/s!Ap6q8W-mvm27g-cURRhiJzcoSEwh_Q