Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Visual filter does not work

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 t...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.