Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) ) ) )
Solved! Go to Solution.
Hi,
Hope this works. Download the file from here.
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
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 🙂
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?
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.
Hi,
Hope this works. Download the file from here.
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.
You are welcome. The ALL() function overrides the filter of Labour day. Yes, the DATESBETWEEN creates a new filter context.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |