Apply ALLSELECTED in a table allowing ALL for a column

[Zaizidor]

Hello!

I have two tables. "Consolidado" and "DatesKO".

"Consolidado" is a table with a column of dates. "Dates KO" is a table with all calendar days in a column and a "Labor Days" column indicating YES or NO.

Consolidado[1.3 - Kick Off Date] column has a relationship with DatesKO[Date]

What I'm trying to do is to generate a cumulative total of the count of dates of Consolidado[1.3 - Kick Off Date]. The code works just fine for that purpose but I have a problem with the filters.

I need ALLSELECTED for DatesKO[Date], but I need to allow ALL for DatesKO[Labor Days]. So, even if Im filtering to show only working days, the formula generates the cumulative total of all working and non working days, but respecting all other filters made before.

I have tried to insert ALL(DatesKO[Labor Days]) inside the calculate formula as an additional filter, but still, doesnt work.

Sorry if this is very basic, I'm not an advanced user. TY!

Kick Off's 2 =
 (
    CALCULATE (
        COUNTA ( Consolidado[1.3 - Kick Off Date] ),
        FILTER ( ALLSELECTED ( DatesKO[Date] ), DatesKO[Date] <= MAX ( DatesKO[Date] ) )
    )
)

[Greg_Deckler]

Sample/example data would be greatly beneficial.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Zaizidor]

DatesKO:

Date	Labor Day
16-mar-18	YES
17-mar-18	NO
18-mar-18	NO
19-mar-18	NO
20-mar-18	YES
21-mar-18	YES

Consolidado:

1.3 - Kick Off Date

16-mar-18

19-mar-18

20-mar-18

Relation is many to one from Consolidado[1.3 - Kick Off Date] to DatesKO[Date]

Considering actual formula the result of the calculation (I am filtering with "YES" on Labor Days) is:

What I want is that COUNTA considers also 19-Mar-18 value, even if its a "NO" in Labor Days, so the value of the final table on 20-mar-2018 and 21-mar-2018 should be 3 and not 2. This should be done respecting other outside filters, that's why I'm using ALLSELECTED.

Hope is clear, thanks in advance 🙂

[Ashish_Mathur]

Hi,

I do not understand the business logic.  Why should the answer be 3 for the last 2 dates and 1 for the first date?

[Zaizidor]

Hello!

Because COUNTA is running through "Consolidado" Table. This table has a value on Mar 16, Mar 19 & Mar 20. Cummulative count should be 1 on day 16, 17, 18, should go up to 2 on day 19, and should go up to 3 on day 20 and after.

Even if Im not showing the day 19 in the final table (cuz of the labor day filter) I want the count to consider that the value exists.

Hope this clarifies. 

[Ashish_Mathur · Accepted solution]

Hi,

Hope this works.  Download the file from here.

[Zaizidor]

I really appreciate it. With my actual knowledge of DAX this would have been impossible for me.

I just changed ALL(DatesKO) for ALLSELECTED (DatesKO), because in my real model I have a larger list of dates that go long before the filter, and I want only the count for the sliced values.

This is amazing. 🙂

One question. Where does the code overrides the filter of Labor Day? If I'm understanding correctly, the function datesbetween() ignores outside filters right? and creating a new filter context.

[Ashish_Mathur]

You are welcome.  The ALL() function overrides the filter of Labour day.  Yes, the DATESBETWEEN creates a new filter context.