[Date slicer] My measure disappear when I filter by year

[Yacine_VC]

Hello everyone,

I'm facing an issue I can't seem to fix : I have two measures, the first one is measuring the number of trainees who attended a training, the second is measuring those who didn't : it's a simple matter of counting the number of "yes" or "no" in a string column.

There's also a date column, with a date for row concerning a person who's been trained, but a "null" if the person wasn't trained.

Each date column (there's 3 of them) is connected to a date table. One has an active relationship, the others have an inactive relationship.

My problem is put the measures on a graphic and I filter the graphic per year, the people who didn't attend the training disappear completely. This is probably because at their row, the date column indicates "null". I tried for this specific measure but that didn't work :

Nb non-formés écoconception = CALCULATE(
    CALCULATE(DISTINCTCOUNT(Formations[Salarié - ID) - 
    CALCULATE(COUNTROWS(Formations), Formations[Formation écoconception]="OUI"), 
    ALLSELECTED(Formations[Date de formation écoconception])
    ),
    ALL(Formations[Date de formation écoconception])
    )

So what I'd like to do is to measure the number of people who didn't attend a formation (total rows - number of rows with a "yes" in the formation column), and that it could adapt to the filter :

- If in 2023, on a total of 30 trainees, 5 had a training, that would make 30-5.

- If in 2024, it was 15 people who had a training, that would be 30-15.

And of course, the measure would appear on the graphic despite the year filter.

I would appreciate your help a lot with that case.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Yacine_VC]

Hi,

Thanks for you answer. Here's a link to my OneDrive where you will find a sample of the date and what I've done with it in Power BI : Partage PBI

Best regards.

[Ashish_Mathur]

Hi,

Since the column/table names are not in English, i cannot understand anything there.  Please upload the English version.  Also, remove the visuals/tables/measures that are working fine so that we can focus on the problematic visual.

[rajendraongole1]

Hi @Yacine_VC - You can use this measure  it contains "OUI" for those who attended as below:

Nb formés écoconception =
CALCULATE(
DISTINCTCOUNT(Formations[Salarié - ID]),
Formations[Formation écoconception] = "OUI"
)

use allselected measure to clear only the date filter from the specific training date column and keep other filters intact

Nb non-formés écoconception =
CALCULATE(
DISTINCTCOUNT(Formations[Salarié - ID]),
FILTER(
ALLSELECTED(Formations),
ISBLANK(Formations[Date de formation écoconception]) || Formations[Formation écoconception] <> "OUI"
)
)

Hope the above measure helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Yacine_VC]

Hello rajendraongole1, thanks for your answer. Unfortunately it doesn't work : I have en error message telling me "DAX operations do not support the comparison of date and text values”. It recommends to use VALUE or FORMAT to convert those values, but I don't think that will help in that case.