Forum Discussion

sminonese's avatar
sminonese
Frequent Visitor
9 years ago
Solved

Query comparing continuous variables

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 fi...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    sminonese
    Frequent Visitor
    9 years ago

    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()
)
)