Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Nad_PBI_21
Frequent Visitor

If Values in column A are equal to any non blank values of Column B, return "1"

Dear All,

 

I am not sure this is feasible this way

I wish to find all the match between two columns by comparing each line of "Customer" to the entire column "RelatatedCust":

if any non blanks value from column "RelatedCust" is found in column "Customer" then return 1 in new column "Related Articles"

 

What I am triing to achieve overall is to find all related "Service":

By selcting one service (ex: "Ananas") you could see all the associated services from all the customer taking that specific service "Ananas"

If I can answer the above issue, I can then put a fitler in the new column "Related Articles"

I am happy if you have another way, 

 

Thanks a lot for your help

NAD

 

Link to the file: https://www.dropbox.com/s/u0n8heaxvgltz32/Articles%20Analysis%20-%20shared.pbix?dl=0

 

Nad_PBI_21_0-1685177654135.png

 

4 ACCEPTED SOLUTIONS
Ahmedx
Super User
Super User

pls try this

Related Articles = 
VAR _t1 = MAX('SelectService'[Service])
VAR _tbl = SELECTCOLUMNS(FILTER(ALL(Data),'Data'[Service]=_t1),"dd",[Customer])
RETURN IF(INT(
MAX('Data'[Customer]) in _tbl) ,MAX('Data'[Customer]))

Screen Capture #1172.png

View solution in original post

honestly, I didn't understand you.
My measure works without yours and it will show all the match. why obyadit her with your measure, I do not understand

View solution in original post

@Ahmedx 

thank you very much, it works well!!

sorry for the confusion

View solution in original post

pls try this for multi select from the slicer

Related Articles = 
VAR _t1 = VALUES('SelectService'[Service])
VAR _tbl = SELECTCOLUMNS(FILTER(ALL(Data),'Data'[Service] in _t1),"dd",[Customer])
RETURN IF(INT(
MAX('Data'[Customer]) in _tbl) ,MAX('Data'[Customer]))

 

View solution in original post

7 REPLIES 7
Ahmedx
Super User
Super User

pls try this

Related Articles = 
VAR _t1 = MAX('SelectService'[Service])
VAR _tbl = SELECTCOLUMNS(FILTER(ALL(Data),'Data'[Service]=_t1),"dd",[Customer])
RETURN IF(INT(
MAX('Data'[Customer]) in _tbl) ,MAX('Data'[Customer]))

Screen Capture #1172.png

Great it works !! thanks a lot @Ahmedx 

Would you see a way to merge my 2 meaures into one, the idea is to have a table that looks like this : where I could rank the "related articles" (ideally the columns of the mesure should not be visible

Thank you very much

Nad_PBI_21_0-1685196777313.png

Here are the 2 measures: 

Related Articles =
VAR _t1 = MAX('SelectService'[Service])
VAR _tbl = SELECTCOLUMNS(FILTER(ALL(Data),'Data'[Service]=_t1),"dd",[Customer])
RETURN IF(INT(
MAX('Data'[Customer]) in _tbl) ,MAX('Data'[Customer]))
 
RelatedCust =
VAR Sele = SELECTEDVALUE ( SelectService[Service] )
VAR Serv = SELECTEDVALUE (Data[Service])
VAR Cust = SELECTEDVALUE(Data[Customer])
RETURN
    IF (Serv = Sele, Cust, ""  )

honestly, I didn't understand you.
My measure works without yours and it will show all the match. why obyadit her with your measure, I do not understand

 

 

@Ahmedx 

Last questions and thank you for your time

- Would you see a way to have multi select from the slicer

- Could the measure works with filter on the page : exemple date: Year (when I tested it, seems that the results where a not right)

 

 

pls try this for multi select from the slicer

Related Articles = 
VAR _t1 = VALUES('SelectService'[Service])
VAR _tbl = SELECTCOLUMNS(FILTER(ALL(Data),'Data'[Service] in _t1),"dd",[Customer])
RETURN IF(INT(
MAX('Data'[Customer]) in _tbl) ,MAX('Data'[Customer]))

 

All good, thank you very much !

@Ahmedx 

thank you very much, it works well!!

sorry for the confusion

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors