Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
HassanAshas
Helper V
Helper V

DAX Measure not Filtering Results as it should be (when sliced by another attribute)

Hi, I have a dataset of employees working on month-to-month basis, 

The dataset looks something like this, 

 

DateEmp CodeProject CodeAllocation
7/1/2022 0:001A0
8/1/2022 0:001A0
9/1/2022 0:001A1
10/1/2022 0:001A1
11/1/2022 0:001A0.2
12/1/2022 0:001A0
7/1/2022 0:002B1
8/1/2022 0:002B1
9/1/2022 0:002B1
10/1/2022 0:002B1
11/1/2022 0:002B0.2
12/1/2022 0:002B1

 

I have a Slicer for Date Range from which the user can select any date range and data will be filtered accordingly. 

 

I have a Matrix Visual that shows allocation of employees month-wise. There is an additional Filter is applied on this Matrix visual, that it only shows those employees which have been allocated (have allocation > 0) on all months being filtered. For example, If I am filtering from August to December, then only the records which have allocation greater than 0 FOR ALL THE MONTHS (August to December) will be shown. I have also attached two examples below to demonstrate this, 

 

HassanAshas_0-1674919807135.png

 

(As only the employee with Emp ID = 2 was allocated on all the months from July to December, so his record is shown only) 

 

HassanAshas_1-1674919853057.png

(As both the employees with Emp ID 1 and Emp ID 2 are allocated in ALL THE MONTHS from September to December (filtered range of date), so their records are shown.

 

This is the measure that I am using currently in this Matrix Visual to filter out the employees which don't have allocation > 0 for all the months being filtered, 

 

 

 

Employee Allocated All Months Boolean = 
Var _count = 
        CALCULATE(
            DISTINCTCOUNT('Calendar'[Month]), 
            FILTER(
                ALLSELECTED(Competency),
                Competency[Emp Code] = MAX(Competency[Emp Code])
            )
        )
    Var _total = 
        CALCULATE(
            DISTINCTCOUNT('Calendar'[Month]), 
            ALLSELECTED(Competency)
        )
RETURN 
    IF(_count = _total, 1)

 

 

 

My problem comes in when I filter the data table, i.e., when I filter the Project. So, right now, Emp ID is working in Project B and Emp ID 2 is working on Project A. When I filter the Report by Project A, my dataset will now contain records of only Emp ID = 1. 

 

And now, it starts giving incorrect results. That is, when I am slicing by the Date (July to December), it should show me Blank (that is, no records) because no Emp matches the Condition that I am applying. However, it instead shows me Emp ID 1 and only shows the "three months" in which it was allocated (neglecting the other months altogether). That is what I do not want it to do. 

 

HassanAshas_2-1674920262034.png

 

I have tried to fix this in all the ways I could, but still couldn't do it. I tried to use "ALL" with the table in the Calculate but it didn't work for me, I don't know why. 

 

Can anyone help in resolving this issue? Thanks a lot in advance.

 

If you would like to use the Power BI File, then you can download it from here: https://drive.google.com/file/d/1X31vAnPlS7gnkHEfcveYpg_wQwE1sKLh/view?usp=sharing

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @HassanAshas 

 

Interesting problem 🙂

I have attached a modified PBIX to this message with my suggestions.

 

My general comments are:

  1. When visual level filters are applied to a matrix visual using a measure, they filter only the fields on the rows of the matrix. Personally, I find this confusing, so I generally avoid applying visual level filters to matrix visuals using measures.
  2. As a safer alternative in this case, I would recommend creating a calculation group that captures the logic for filtering Employees. A calculation group would have the added benefit of being able to be applied to any measures that might be used in visuals, so the filtering logic only needs to be written once.
  3. To make this filtering logic more robust, I suggest adding a Start of Month (or similar) column to Calendar.
  4. With such a calculation group applied via filtering on a calculation item, the Total Allocation measure can then be rewritten more simply, and Total Unique Resources can be used directly in the card visual.

Based on these points, the changes I made were:

1. Add a Start of Month column to Calendar:

Start of Month = 
EOMONTH ( 'Calendar'[Date], -1 ) + 1

2. Create the following calculation group called Employee Filter, containing a single calculation item Employee Allocated All Months. Below is DAX script from Tabular Editor 3. If you're using Tabular Editor 2, create a calc group called Employee Filter and enter the relevant as the expression. Take a look at the attached PBIX in any case.

I slightly modified your original logic. If the selected date range overlaps any part of a month, then that month is included, and only Employees allocated to all included months will survive the filter.

---------------------------------------
-- Calculation Group: 'Employee Filter'
---------------------------------------
CALCULATIONGROUP 'Employee Filter'[Employee Filter Option]

    CALCULATIONITEM "Employee Allocated All Months" = 
        VAR NumMonthsAllselected =
            CALCULATE (
                DISTINCTCOUNT ( Calendar[Start of Month] ),
                ALLSELECTED ( 'Calendar' )
            )
        VAR FilteredEmployees =
            FILTER (
                VALUES ( Competency[Emp Code] ),
                VAR RelatedMonths =
                    CALCULATE (
                        -- Optimized Related Distinct Count
                        -- See here: https://www.daxpatterns.com/related-distinct-count/
                        SUMX ( SUMMARIZE ( Competency, 'Calendar'[Start of Month] ), 1 ),
                        ALLSELECTED ( 'Calendar' )
                )
                RETURN
                    RelatedMonths = NumMonthsAllselected
            )
        RETURN
            CALCULATE (
                SELECTEDMEASURE (),
                KEEPFILTERS ( FilteredEmployees )
            )

3. Add a page level filter: Employee Filter Option = "Employee Allocated All Months"

OwenAuger_0-1674997822990.png

 

4. Remove visual level filter using Employee Allocated All Months Boolean from the matrix visual.

5. Use Total Unique Resources in the left card.

6. Rewrite Total Allocation more simply as:

Total Allocation = 
AVERAGEX (
    VALUES ( 'Calendar'[Start of Month] ),
    [Allocation %]
)

 

The result is what I think you were looking for. For example, selecting July-December with Project Code A gives blank results:

OwenAuger_1-1674998533193.png

 

Please post back if needed. It's quite possible the logic may need tweaking!

 

Regards

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

Hi @HassanAshas 

 

Interesting problem 🙂

I have attached a modified PBIX to this message with my suggestions.

 

My general comments are:

  1. When visual level filters are applied to a matrix visual using a measure, they filter only the fields on the rows of the matrix. Personally, I find this confusing, so I generally avoid applying visual level filters to matrix visuals using measures.
  2. As a safer alternative in this case, I would recommend creating a calculation group that captures the logic for filtering Employees. A calculation group would have the added benefit of being able to be applied to any measures that might be used in visuals, so the filtering logic only needs to be written once.
  3. To make this filtering logic more robust, I suggest adding a Start of Month (or similar) column to Calendar.
  4. With such a calculation group applied via filtering on a calculation item, the Total Allocation measure can then be rewritten more simply, and Total Unique Resources can be used directly in the card visual.

Based on these points, the changes I made were:

1. Add a Start of Month column to Calendar:

Start of Month = 
EOMONTH ( 'Calendar'[Date], -1 ) + 1

2. Create the following calculation group called Employee Filter, containing a single calculation item Employee Allocated All Months. Below is DAX script from Tabular Editor 3. If you're using Tabular Editor 2, create a calc group called Employee Filter and enter the relevant as the expression. Take a look at the attached PBIX in any case.

I slightly modified your original logic. If the selected date range overlaps any part of a month, then that month is included, and only Employees allocated to all included months will survive the filter.

---------------------------------------
-- Calculation Group: 'Employee Filter'
---------------------------------------
CALCULATIONGROUP 'Employee Filter'[Employee Filter Option]

    CALCULATIONITEM "Employee Allocated All Months" = 
        VAR NumMonthsAllselected =
            CALCULATE (
                DISTINCTCOUNT ( Calendar[Start of Month] ),
                ALLSELECTED ( 'Calendar' )
            )
        VAR FilteredEmployees =
            FILTER (
                VALUES ( Competency[Emp Code] ),
                VAR RelatedMonths =
                    CALCULATE (
                        -- Optimized Related Distinct Count
                        -- See here: https://www.daxpatterns.com/related-distinct-count/
                        SUMX ( SUMMARIZE ( Competency, 'Calendar'[Start of Month] ), 1 ),
                        ALLSELECTED ( 'Calendar' )
                )
                RETURN
                    RelatedMonths = NumMonthsAllselected
            )
        RETURN
            CALCULATE (
                SELECTEDMEASURE (),
                KEEPFILTERS ( FilteredEmployees )
            )

3. Add a page level filter: Employee Filter Option = "Employee Allocated All Months"

OwenAuger_0-1674997822990.png

 

4. Remove visual level filter using Employee Allocated All Months Boolean from the matrix visual.

5. Use Total Unique Resources in the left card.

6. Rewrite Total Allocation more simply as:

Total Allocation = 
AVERAGEX (
    VALUES ( 'Calendar'[Start of Month] ),
    [Allocation %]
)

 

The result is what I think you were looking for. For example, selecting July-December with Project Code A gives blank results:

OwenAuger_1-1674998533193.png

 

Please post back if needed. It's quite possible the logic may need tweaking!

 

Regards

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger This worked like a Charm! Thank you! 

Unfortunately, however, I am not able to understand the logic still lol, and it is causing several problems, solutions of which I don't seem to find. 

 

First of all, due to the Start of Month column, I am now unable to Use the Date Slicer properly. That is, if I select any date other than "First Day of the Month", my filter doesn't work. 

 

HassanAshas_0-1675095795601.png

 

 

Like in the given picture above. 

 

I tried to change up the things a bit but I couldn't figure out much. I am not familiar with Calculated Groups and the Tabular Editor and hence, didn't manage to understand the code you typed. (I was searching on Tabular Editors, Calculated Groups etc since this post. I have basic understanding of them now, I also used it a bit but still couldn't figure out the problem 😕

Also, I found a weird problem that if I directly use the measure Allocation % in the Total Allocation measure, it doesn't work. 

That is, if I change my Total Allocation measure to this, 

 

 

 

 

Total Allocation = 
// AVERAGEX (
//     VALUES ( 'Calendar'[Start of Month] ),
//     [Allocation %]
// )

AVERAGEX (
    VALUES ( 'Calendar'[Start of Month] ),
    SUM(Competency[Allocation])
)

 

 

It gives me incorrect result,

HassanAshas_1-1675096257532.png

 

This looked really weird to me. (I tried to use this approach in my original dataset which is quite huge, and everything worked except this measure. So I tried to change it up a little bit but ended up with more problems) 

 

Anyhow, I really really appreciate you for taking out time and giving such a detailed answer! Your answer provided me with a new insight on why not to use Measures as visual level filter with the Matrix visual and it also definitely provides the solution to the actual problem of mine and now I probably need to tweek the logic a bit to get things straight for myself. Need to study Calculation Groups in more detail. Thank you so much! 

Hi again @HassanAshas 

There are a few things going on here.

When using an iterator like AVERAGEX, the 2nd argument is evaluated in the "row context" of each row of the table provided as the 1st argument. In order for the row context to be applied as a filter (i.e. converted to filter context), you need to either wrap the expression in CALCULATE or reference a measure (which is automatically wrapped in CALCULATE). This is known as "context transition". See this article. 🙂

 

In this example, you would have to write this:

 

Total Allocation = 
// AVERAGEX (
//     VALUES ( 'Calendar'[Start of Month] ),
//     [Allocation %]
// )

AVERAGEX (
    VALUES ( 'Calendar'[Start of Month] ),
    CALCULATE ( SUM ( Competency[Allocation] ) )
)

 

 

The other issue to do with which months are included is a little trickier, and it looks like the logic I used wasn't quite what you wanted. Do you only want to treat a month as included if the 1st day of  that month is within the filtered date range?

 

Maybe post back if you need further help on that last point.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Oh! yes, I am aware of Context Transition. Only the concept, never really thought it would be happening in this case haha. Thanks so much for pointing this out! Made me think of this from a whole different angle (and now looking at it, I totally get how Context Transition was altering the result of the Measure). I really really appreciate you taking time out and helping out in such a detailed manner. 


The measure, still is not working for a larger dataset and surely this example data that I made is not able to replicate the problem I am facing with original dataset. The problem is not due to Context Transition in the original dataset (as far as I analyzed it). Time for me to figure out the problem and see what I can do with it lol. I will spend some time in figuring out the issue, and if I still can't solve it then will try to replicate the issue with another copy of original dataset and will see if that could help maybe. 

 

As for the Months Problem, what I actually need is if the Date Filter is set from "one month" to "another month", then it should consider all the data from the first month to second month.

 

For example, if I select the following dates, 05-08-2022 and 06-11-2022 then it should consider all the data from August to November. The problem I was facing was this that due to the Start of Month column used in DAX of Calculated Field (I think), I wasn't able to fulfill the above condition. That is, I selected the range of 23-08-2022 to 1-12-2022 but it didn't show me any result (while in reality, it should have shown all the data from August to December). 

HassanAshas_0-1676013969788.png

 

However, If I changed the starting date to the First Day of August (i.e., 01-08-2022) then it worked correctly (probably due to the Start Of Month being used in DAX) 

 

But, I managed to find a workaround this, lol. I made a Month-Year column in the Calendar Table, 

 

Month Year = FORMAT([Date],"mmmm yyyy")

 

And then changed its Datatype to "Date" (so that I can use Relative Date Filter). And using this, this problem got fixed. For example,  I selected the range from 11-08-2022 to 20-10-2022. 

HassanAshas_1-1676014314522.png

Now, it does work and shows the result between that range (unfortunately however, I would have ideally wanted the results of "August" also being included because my first date is of August month, but because all my data has the dates of "First Day of Month", so that's why August data is not being included. But at least it fixed that issue of not having to always select first day of the month) 

 

Anyhow, thanks a lot once again for such a massive help, I am sure I would never have ever figured this out myself. It's quite shocking that people go to such lengths to help the other person out in this forum. I would be trying my best to pay back now by trying to help any person that I can in this forum (probably can't do it now considering my limited knowledge lol, but in future, surely!) 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors