Visual filter does not work

[Anonymous]

Hi all,

I am trying to write simple DAX calculation. But the visual filter does not work as expected for DAX calculations.

Simple DIVIDE calculation works fine, as below.

But with the conditional DIVIDE calculation the filter added to visual does NOT work.

Thanks in advance for any help.

Best,

-Shirish

[Ashish_Mathur]

Hi,

What exactly is the problem?  What result are you expecting?  Explain the business context.

[Anonymous]

Thanks Ashish.
Here is the scenario,
I have 2 tables (Calendar Table and Compliance Table) as below and join using MonthDate,
Calendar Table as,
MonthDate Flag
1/1/2021 Y
12/1/2020 Y
11/1/2020 Y
10/1/2020 N
Compliance Table as,
MonthDate InComplianceCount TotalCount
1/1/2021 0 0
12/1/2020 4 4
11/1/2020 2 2
10/1/2020 4 4

And compliance percentage as,
% Compliance = DIVIDE(SUM(Compliance[InCompliance]),SUM(Compliance[TotalCases]))

When I create visualization (chart or table) with Flag = "Y" and Values as MonthDate, InComplianceCount, TotalCount then vislization has 3 rows of 1/1/2021, 12/1/2020, 11/1/2020.
But, with values as MonthDate, % Compliance then I see ONLY 2 rows 12/1/2020 and 11/1/2020.

Figured out missing 3rd rows as for 1/1/2021 the totalcount is "0". Tried various options with no luck.

Any help is greatly appriciated.

Thanks,
-Shirish

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks Ashish for all your help and time.

I am trying to see if we can avoid using the slicer objects. 

I created 2 variable as,

var ComplianceCount = CALCULATE(sum(Compliance[InComplianceCount]), KEEPFILTERS('DIM-Date'[Previous3MonthPriorYearTrendFlag]="Y"))

var TotalCount = CALCULATE(sum(Compliance[TotalCount]), KEEPFILTERS('DIM-Date'[Previous3MonthPriorYearTrendFlag]="Y"))

when I return "ComplianceCount" I see dates are filtered correctly as below.

But if I add once condition as below,

var CompTest = if(TotalCount=0,1,2)

and return "CompTest" I see dates filter is removed and all the dates are displayed as below.

I am trying to figure out the reason.

Thanks,

-Shirish

[Anonymous · Accepted solution]

Hi @Anonymous 

I have test on your sample. I think your problem should be caused by ComplianceMonth column in your visual. ComplianceMonth column in your visual should be from Compliance table. However your filter Flag comes from related table Calendar. 

So if you use ComplianceMonth column from Compliance table, the measure will show an incorrect result.

Try to use the column in Calendar table instead of the column in Compliance .

My test:

You see above table visual in red box will show incorrect results like yours.

Visuals as below which use MonthDate in Calendar table will show correct result.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

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