solution for the First time name and has targeted value

[IsaLe]

Hi,
I am struggling with this!!!my ideal is create a table of NAME that appear only one time in the merge table, then check if it has value =10. Bingo!!! Unfortunately, i have not succeeded. please help ASAP. Thanks in advance.
Table1[col1] with value like 1,2,3...10
Table2[name] with value text1,text2...textN
Relationship is 1 to star(*), with start at the side of table2
I am looking for a result like:
Table3[name][col1]
Text1 10
TextN 10

Note: text1 and textN have the value=10 and both are appear only 1 time in the mergeTable( remember the relationship 1&star)
MergeTable
Text1 10
Text2 1
Text2 5
Text2 10
Text3 10
Text3 9
TextN10
...

[v-juanli-msft · Accepted solution]

Hi @IsaLe 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @IsaLe 

I make a test as below:

Table 1 and Table 2 

Create such relationship

In Table1, create a calcuated column with formula

Week = WEEKNUM([date],2)

Then in Table2, create a measure

Measure = var minvalue=CALCULATE(MIN(Table1[Week]),ALLEXCEPT(Table2,Table2[product])) return IF(minvalue=10,minvalue,BLANK())

Add column and measure in a table visual

If your scenario is different, please show me example data for analysis.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @IsaLe 

Which column do the Table1 and Table 2 have a relationship based on?

Best Regards
Maggie

[IsaLe]

Hi Maggie,

table 1 and table 2 is One to Many relationship. In the original table, table 1 is about date and table 2 is about product. It is linked by the commun column 'week of date' in the format of DD/MM/YYYY.

Anyhow, i managed to do it but i dont think it is the best way to do it. What i did was

- Use GROUPBY function to find a first week of every product (in our context, the minimun number of each 'name')

- Use IF to check if the result of the previous function is equal to 10 then it is what iam looking for.

As you see, use GROUPBY and IF it is a 2n computation time. 

If you know a logic how to get a result of all the name, which appear only one time. Then i ll compare it to number 10. it ll be a better solution in term of complexity.

thanks