Query comparing continuous variables

[sminonese]

Hi,

I am looking for a tip on how to solve this. As far as I can tell I will need some DAX magic.

I have two tables: Samples and Quality.

Samples have a unique ID field and two numeric fields for two analytical parameters.

Quality has an ID field and two numeric fields for each of the analytical parameters, describing the lower and upper limits for that quality.

I wish to generate a table that, for each Quality (A,B,C) lists matching Samples. This should be done by testing

{Quant1 >=Quant1_Low and Quant1<=Quant1_High} and {Quant2 >= Quant2_Low and Quant2<=Quant2_High}.

If True, append a row with the Quality and SampleID, continue to next sample. At the end of the samples list, go to the next Quality and go through the Samples list again.

Data tables and desired query result

Isn't this a classical problem to solve in database queries? I'm a complete newbie so I don't immediately see how to solve this in Power BI. Of course I don't want to hardcode the "Quality" table, it should be possible to append Qualities or change the values.

Thank you for any help!

[Anonymous · Accepted solution]

Hi @sminonese,

You can refer to bleow steps if it suitable for your requirement:

1. Use "SELECTCOLUMNS" and "CONCATENATEX" function to add the quality tag.

Summary Table = SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],","))

2. Use variable to store above table and use SUBSTITUTE function to convert "Quality" column to new columns.

Summary Table = 
var temp=SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],","))
return
    SELECTCOLUMNS(temp,"SampleID",[SampleID],"LEFT", LEFT(SUBSTITUTE([Quality],",","-"),SEARCH("-",SUBSTITUTE([Quality],",","-"))-1),"RIGHT",RIGHT(SUBSTITUTE([Quality],",","-"),LEN(SUBSTITUTE([Quality],",","-"))-SEARCH("-",SUBSTITUTE([Quality],",","-"))))

3. Use SELECTCOLUMNS and UNION function to merge left and right part.

Summary Table = 
var temp=SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],","))
return
UNION(
    SELECTCOLUMNS(temp,"SampleID",[SampleID],"Quality", LEFT(SUBSTITUTE([Quality],",","-"),SEARCH("-",SUBSTITUTE([Quality],",","-"))-1)),
    SELECTCOLUMNS(temp,"SampleID",[SampleID],"Quality",RIGHT(SUBSTITUTE([Quality],",","-"),LEN(SUBSTITUTE([Quality],",","-"))-SEARCH("-",SUBSTITUTE([Quality],",","-"))))
  )

Regards,

Xiaoxin Sheng

[sminonese]

Thank you Xiaoxin! This is an excellent answer.

I have tweaked the code a little bit to allow tagging up to 3 "Quality"-tags and removing blanks. Additionally I noticed another tip to use PATHITEM to split into columns by the pipe delimiter, so I can skip the substitution step.

An improvement would be to have a default tag such as "Other/Offspec" to fall back when there is no matching Quality spec. A second improvement would be to support arbitrary number of tags, right now only supporting up to 3.

QualityTags = 
var temp = SELECTCOLUMNS(Samples;"SampleID";[SampleID];"Quality";CONCATENATEX(
    FILTER(
        ALL('Quality Specifications');
        [Quant1]>=[Quant1_Low]&&
        [Quant1]<=[Quant1_High]&&
        [Quant2]>=[Quant2_Low]&&
        [Quant2]<=[Quant2_High]
        );
        [Quality];"|"))
return
UNION(
FILTER(
    SELECTCOLUMNS(temp;"SampleID";[SampleID];
    "Quality";PATHITEM([Quality];1)
    );
    [Quality1]<>BLANK()
);
FILTER(
    SELECTCOLUMNS(temp;"SampleID";[SampleID];
    "Quality2";PATHITEM([Quality];2)
    );
    [Quality2]<>BLANK()
);
FILTER(
    SELECTCOLUMNS(temp;"SampleID";[SampleID];
    "Quality3";PATHITEM([Quality];3)
    );
    [Quality3]<>BLANK()
)
)