Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good afternoon everyone,
I'm struggling because I can not fin the solution for this problem:
This is my main table named 'DETALLES EMPLEADOS' (I have filtered it to ease the problem):
Okay so, as you can see there are three columns with dates but we are going to focus in the last two 'NUEVA FECHA CORREGIDA' and 'FIN ULT CUPO'.
I have also created a date table named 'CALENDAR'. This table has got only a column named 'DATE' whose first date is 01/01/2015 and the last one 31/12/2023 because I need to connect the data from my main table to this one to create a date filter in the slide.
The goal of my slide is to create a date filter like the attached below which could count the rows from the main table following the next rules:
Obviously the dates of the filter come from the date table that I've created and connected with the main table previously.
I have tried the following DAX but it's not throwing back the expected result:
Hope you could understand my problem and help me..
Kind regards,
Adrian
Solved! Go to Solution.
Hi again @d_gosbell !
I resolved the problem yesterday
I created two more date tables, one of them contain all the dates from 'NUEVA FECHA CORREGIDA' and the other one the dates from 'FIN ULT CUPO'. Then, I wrote this DAX formula:
By doing > min date or <= min date your condition on the 'DETALLES EMPLEADO'[NUEVA FECHA CORREGIDA] column will return true all the time, so basically what your expression is evaluating is the following:
Hi @d_gosbell !
Firstly, thank you for your answer!
Yes, I thought the same yesterday because the DAX isn't throwing back the employees whose contract start before the minimum date selected so.. should I maybe try to divide the DAX formula in two parts? I mean:
What do you think?
Regards,
Adrian
@Anonymous wrote:
Hi @d_gosbell !
so.. should I maybe try to divide the DAX formula in two parts? I mean:
- First part: [NUEVA FECHA CORREGIDA] >= MIN('CALENDARIO PRUEBA'[DATE]) && 'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].
- Second Part: [NUEVA FECHA CORREGIDA] < MIN('CALENDARIO PRUEBA'[DATE]) && 'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].
What do you think?
I think this will probably produce the same result as the existing measure.
Can you try expressing your question as a set of example data?
So if the slicer was set to a min date of 1 July 2019 and a max date of 31 August 2019 - Can you show tell us which of the example rows of data below you expect to be counted by adding a yes/no in the included column in the table below?
NUEVA FECHA CORREGIDA | FIN ULT CUPO | Included? |
15 Jul 2019 | 15 Aug 2019 | |
15 Jun 2019 | 15 Sep 2019 | |
10 Jun 2019 | 10 Jul 2019 | |
20 Jul 2019 | 20 Sep 2019 | |
23 May 2019 | 23 Jun 2019 | |
25 Sep 2019 | 25 Oct 2019 |
Hi again @d_gosbell !
I resolved the problem yesterday
I created two more date tables, one of them contain all the dates from 'NUEVA FECHA CORREGIDA' and the other one the dates from 'FIN ULT CUPO'. Then, I wrote this DAX formula:
User | Count |
---|---|
80 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |