How to Convert SQL Query to DAX query

[SantoshKumar]

Hi All,

I have a below requirement to Convert an Sql Query to DAX query (result can be a new column or new measure).

note: as i am bound to data protection policy so could not provide the actual sql query.

Background of Query:

 1.there are two tables Table-A and Table-B, out of which Column1 is same for Both the tables and  other columns are different.

2. I am looking to extract Column1 values by applying the below mentioned conditions.

select TABLE-A.Column1
from TABLE-A inner join TABLE-B
on TABLE-A.Column1 = TABLE-B.Column1
where TABLE-B.Column2 = 'XYZ'
and TABLE-A.Column3 = 'ABC' and TABLE-A.Column4 not like '%ASD%'
and TABLE-A.Column4 not like '%QWE%' and TABLE-A.Column4 not like '%JKLNM%'

As per my  knowledge, i have used LookUp , FILTER , search options of Power Biindividually  but could not able to figure out to use them in a combination

It would be a great help if anyone provides any solution to my request.

Thanks

Santosh

[Icey · Accepted solution]

Hi @SantoshKumar ,

Kindly check the attachment whether help.

Measure = 
VAR t =
    FILTER (
        ADDCOLUMNS ( 'Table-A', "B_Col2", RELATED ( 'Table-B'[Column2] ) ),
        [B_Col2] = "XYZ"
            && [Column3] = "ABC"
            && SEARCH ( "ASD", [Column4],, 0 ) = 0
            && SEARCH ( "QWE", [Column4],, 0 ) = 0
            && SEARCH ( "JKLNM", [Column4],, 0 ) = 0
    )
RETURN
    CONCATENATEX ( t, [Column1],", " )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SantoshKumar]

@Icey 

Thank you, Your Solution worked for me with small minor changes as per my requirement.

Regards

Santosh

[SantoshKumar]

@Icey
Sorry but i have not got the required solution from your response. i will update some data in your pbi file and try to explain my requirement again.

[amitchandak]

@SantoshKumar , you need to join both tables and column1 and then create a measure to count column1 and plot it with column1 un summarized

example

calculate(count(TABLE-A[Column1]),TABLE-B[Column2] = "XYZ" , TABLE-A[Column3] = "ABC" ,CONTAINSSTRING(TABLE-A[Column4],"ASD")
,not(CONTAINSSTRING(TABLE-A[Column4],"QWE")) ,not(CONTAINSSTRING(TABLE-A[Column4],"JKLNM")))

[SantoshKumar]

@amitchandak 

Hi Amit,

I got your point about joining the tables and when i tried to do so, it is making changing to my Table-A as per the conditions i applied.

Here Table-A is used to show so other details in my report and when i apply this conditons, i will loose some data which i dont want to.

is der any other options to get the job done?

Thanks

Santosh