DAX measure: string search/find/contains with conditions

[navafolk]

Hi pros,

I have a visual matrix like this:

C_DAY	C_Type
13-Mar-20	Actual
13-Mar-20	No data
14-Mar-20	Forecasted
14-Mar-20	No data
14-Mar-20	No data

I am trying to prepare a measure that can: If a C_DAY contains "Actual", then returns "true" to all rows of this C_DAY.

My failed trial: 

CAT = contains(filter(table,table[C_DAY]=table[C_DAY]),table[C_TYPE],"Actual")

C_DAY	C_Type	CAT	Expected
13-Mar-20	Actual	true	true
13-Mar-20	No data	false	true
14-Mar-20	Forecasted	false	false
14-Mar-20	No data	false	false
14-Mar-20	No data	false	false

Anyone, please help. Thank you.

[amitchandak]

@navafolk , Try a new measure like

if ( countx(Summarize(Table, Table[C_DAY], "_1",countx(filter(Table,Table[C_Type]="Actual"),C_DAY)),[C_DAY])>=1, "True","False")

[AlB]

Hi @navafolk 

Measure =
"Actual" IN CALCULATETABLE ( DISTINCT ( Table1[C_Type] ), ALL ( Table1[C_Type] ) )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[navafolk]

Hi @AlB ,

It works perfectly, but it is quite hard for me to understand how it works. May you explain please?

Thank you again.

[AlB]

Hi @navafolk 

In every row of you table visual (except the Total) both C_Day and C_Type are being filtered. That's your filter context. You want to see all the C_Types for that specific date, so you need to clear the filter on C_Type but keep the one for C_day. That is what we do with

  CALCULATETABLE ( DISTINCT ( Table1[C_Type] ), ALL ( Table1[C_Type] ) )

which yields a one-column table with all the values of C_Type for the date in the current row.

Once we have that, we just need to check if "Actual" is one of those values. That can be done with the IN operator

"Actual" IN CALCULATETABLE ( DISTINCT ( Table1[C_Type] ), ALL ( Table1[C_Type] ) )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[navafolk]

It is clear.

Big thanks @AlB 

[Mariusz]

Hi @navafolk 

Try this

Measure = 
CALCULATE(
    CONTAINS( 'Table', 'Table'[C_Type], "Actual" ),
    ALL( 'Table' ),
    VALUES( 'Table'[C_DAY] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[navafolk]

Thank you @Mariusz, @AlB 

They all work like a charm. Big thanks.