cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## I need the date of a blank count

Hello everyone! I hope you are well, i have the following problem with a data of date (in text format or date format) and the type of work, so, the date I need is the one that has the minimum value, that is, the one where no count was found (means that there was no type "S" work on that date), as shown in the following image

to better test this I made a test table like this

Date                 Type of work

 3/01/2023 M 3/01/2023 M 3/01/2023 S 3/01/2023 S 2/01/2023 M 2/01/2023 M 2/01/2023 M 1/01/2023 M 1/01/2023 S

and i put my matrix with the data

and i tried the following meausure for this but obviously it didn't work for me

date for min work S =
var _summarized = SUMMARIZE(Table_1, Table_1[Date], "var1",
CALCULATE(COUNT(Table_1[Type of work]),FILTER(Table_1, Table_1[Type of work] = "S"))
)
return MINX(
FILTER(
_summarized,
[var1] = MINX(_summarized,[var1])
),
[Date]
)

please, can you help me ?

1 ACCEPTED SOLUTION
Super User

try this @Julux12

``````Measure =
VAR base = tbl
VAR s =
SUMMARIZE ( FILTER ( base, [Column2] = "S" ), [Column1] )
VAR m =
SUMMARIZE ( FILTER ( base, [Column2] = "M" ), [Column1] )
VAR anti =
TOPN ( 1, EXCEPT ( m, s ), [Column1], ASC )
RETURN
MAXX ( anti, [Column1] )``````

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
2 REPLIES 2
Super User

try this @Julux12

``````Measure =
VAR base = tbl
VAR s =
SUMMARIZE ( FILTER ( base, [Column2] = "S" ), [Column1] )
VAR m =
SUMMARIZE ( FILTER ( base, [Column2] = "M" ), [Column1] )
VAR anti =
TOPN ( 1, EXCEPT ( m, s ), [Column1], ASC )
RETURN
MAXX ( anti, [Column1] )``````

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
New Member

Thank you!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.