DAX measures to check compliance using CUSTId from two tables

[mehaboob557]

Dear ALL,

First i am showing how the data in table visual, here sale_stage and table1CUSTID are from table 1 and table2CUSTID and version are from table 2

  

CUSTID is the primary and foriegn keys.

I would like to check,

1.If sales_stage is Warranty_handover or Project_handover then the max version  of CUTSID should be > 2.0

     a) i want to show that data which doesn't not statisfy >2.0 (the above condition)

     b) I want to show that data which satisfies >2.0  

2. If sales_stage is floorplan_uploaded, initial_proposal_sent then the max version of CUTSID should be >1.0 and < 2.0

     a) i want to show that data which doesn't not statisfy >1.0 and < 2.0 (the above condition)

     b) I want to show that data which satisfies >1.0 and < 2.0

Please suggest me how can i achieve this 

[Zubair_Muhammad · Accepted solution]

@mehaboob557

Try this

=
IF (
    OR (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Warranty_handover",
        SELECTEDVALUE ( Table1[sales_stage] ) = "Project_handover"
    )
        && MAX ( Table2[version] ) > 2,
    "Satisfies",
    IF (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Installation"
            && MAX ( Table2[version] ) > 1
            && MAX ( Table2[version] ) < 2,
        "Satisfies",
        "Doesnot Satisfy"
    )
)

[Zubair_Muhammad]

Hi @mehaboob557

Try this MEASURE

=
IF (
    OR (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Warranty_handover",
        SELECTEDVALUE ( Table1[sales_stage] ) = "Project_handover"
    )
        && SELECTEDVALUE ( Table2[version] ) > 2,
    "Satisfies",
    IF (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Installation"
            && SELECTEDVALUE ( Table2[version] ) > 1
            && SELECTEDVALUE ( Table2[version] ) < 2,
        "Satisfies",
        "Doesnot Satisfy"
    )
)

[mehaboob557]

Thank you @Zubair_Muhammad,

Forgot to mention for each CUSTID, i have to look for the maximun version number. 

Now i have edited my question also.

1.If sales_stage is Warranty_handover or Project_handover then the max version  of CUTSID should be > 2.0

     a) i want to show that data which doesn't not statisfy >2.0 (the above condition)

     b) I want to show that data which satisfies >2.0  

2. If sales_stage is floorplan_uploaded, initial_proposal_sent then the max version of CUTSID should be >1.0 and < 2.0

     a) i want to show that data which doesn't not statisfy >1.0 and < 2.0 (the above condition)

     b) I want to show that data which satisfies >1.0 and < 2.0

I mean, want to check the same scenarios with the maximum version of that CUSTID.

Please suugest where can i mention to take MAX version of that CUSTID and check the compliance

Thanks a ton in advance @Zubair_Muhammad

[Zubair_Muhammad · Accepted solution]

@mehaboob557

Try this

=
IF (
    OR (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Warranty_handover",
        SELECTEDVALUE ( Table1[sales_stage] ) = "Project_handover"
    )
        && MAX ( Table2[version] ) > 2,
    "Satisfies",
    IF (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Installation"
            && MAX ( Table2[version] ) > 1
            && MAX ( Table2[version] ) < 2,
        "Satisfies",
        "Doesnot Satisfy"
    )
)

[mehaboob557]

Thank you so much @Zubair_Muhammad

You rock in DAX.  I will accept your solution.

i have some questions in my mind, 

Can we check the same conding in the other way. like,

1. CUSTID's(table1) of sales_stage= warranty, but dont have Quotation_NO in table 2

ex: 

table1

   CUSTID  |  sales_stage | final_cost

   xxx-001 |  warranty     |   1000

   xxx-002 |  warranty     |   1201

table2

 CUSTID  |  version | quotation    | cost

   xxx-001 |  2.3       | quotexxx01 | 1120

   xxx-003|  1.5        | quotexxx90 | 1003

here in the above two table, CUSTID "xxx-002" reached warranty stage but no quotations generated in sales stage. I have to find out that data.

2. Quotations  which  are greater than 2.0 version which dont have matching Final_COST  filled in table1.( tables will be like the above examples)

Any suggestions how can i chieve this.

[Zubair_Muhammad]

Hi @mehaboob557

I believe you can add condition like this to the formula

&& ISBLANK(related(Table2[Quotation]))

[mehaboob557 · Accepted solution]

Hi @Zubair_Muhammad

you mean, 

IF (
    OR (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Warranty_handover",
        SELECTEDVALUE ( Table1[sales_stage] ) = "Project_handover"
    )
        && MAX ( Table2[version] ) > 2,
    "Satisfies",
    IF (
        SELECTEDVALUE ( Table1[sales_stage] ) = "Installation"
            && MAX ( Table2[version] ) > 1
            && MAX ( Table2[version] ) < 2,
        "Satisfies",
        "Doesnot Satisfy"
    )
) && ISBLANK(related(Table2[Quotation]))

This way ? 

[Zubair_Muhammad]

@mehaboob557

2 small questions

Is Warranty stage different than WarrantyHandover?

if there is No related Quotation .. do you want the Result  "Does not SAtisfy"???

[mehaboob557]

 Hi @Zubair_Muhammad

I have 10 sales_stages actually. i have shown only 3 stages for the demo sample.

a)  >= 2.0 versions are 7 stages (like project_handover, Warranty, Snags e.t.c)

b)  between >1.0 and <2.0 versions  will have three stages. (floorplan, installation, intial_amount)

I am creating compliance report to check below scenarios.

1. If sales stages  of above mentioned point "a" have < 2.0 versions in table2 , i have to show that data not satisfied. (i hope this is coming by using your DAX suggestion)

2. If sales stages  of above mentioned point "b" are not between >1.0 and <2.0 versions , i have to show that data not satisfied. (i hope this is coming by using your DAX suggestion)

3.If sales stages  of above mentioned point "a" i.e 7 stages but no quotation_NO is generated(or) no row in the table 2 then i have to show that CUSTID which are not present in table2 

4. If sales stages  of above mentioned point "b" i.e 3 stages(which are between >1.0 and <2.0 versions)  but no quotation_NO is generated(or) no row in the table 2 then i have to show that CUSTID which are not present in table2

Main concept to check the error report in these two tables.

Please let me know u have any queries